Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Angry Unanswered: Select max() works from command line, not from servlet

    Hey John --

    I have a column in a postgres table, called photo_id. I can tell it exists because if I do a "describe table", I see the following:

    Table "images"
    Column | Type | Modifiers
    ----------------+-----------------------+---------------------------------------------
    photo_id | integer | not null default nextval('photo_seq'::text)
    length | integer | not null
    satmean | integer | not null
    intensitymean | integer | not null
    subtopic | character varying(60) |
    keyworda | character varying(60) |
    keywordb | character varying(60) |
    keywordc | character varying(60) |
    clientname | character varying(60) |
    designyear | date |
    filename | character varying(60) |
    primarycolor | character varying(60) |
    secondarycolor | character varying(60) |
    file | bytea |
    topic | character varying(60) |

    However, when my servlet calls:

    select max( photo_id ) from images

    it gets this error:

    SQL EXCEPTION!: The column name photo_id not found.
    SQL EXCEPTION!: null
    SQL EXCEPTION!: 0

    The weird thing is, that same " select max" statement works just fine when typed into the command-line client. Any idea what I'm doing wrong? Or more to the point, know of any other way I can select that last id inserted?

    Thanks so much -- Matt

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    " photo_id | integer | not null default nextval('photo_seq'::text)"

    Erm shouldnt u create a sequence called:

    create sequence photo_id_seq;

    i assume ur sequence now was created with:
    create sequence photo_seq;

    the column photo doesnt excists in ur table and thus cant be incremented.

    I could be wrong tho, because i have no idea what the ::text part does.
    If i had to create the table i'd would do the following:

    create sequnce photo_id_seq;

    CREATE TABLE <tablenaam>
    (
    photo_id int primary key not null,
    .....)

    and with an insert i would do a simple:
    insert into <tablename> values (nextval('photo_id'),<rest of the values>

    But then again, im a dba noobie.

    -Ed

  3. #3
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Select max() works from command line, not from servlet

    Originally posted by decoybox_6
    Hey John --

    I have a column in a postgres table, called photo_id. I can tell it exists because if I do a "describe table", I see the following:

    Table "images"
    Column | Type | Modifiers
    ----------------+-----------------------+---------------------------------------------
    photo_id | integer | not null default nextval('photo_seq'::text)
    length | integer | not null
    satmean | integer | not null
    intensitymean | integer | not null
    subtopic | character varying(60) |
    keyworda | character varying(60) |
    keywordb | character varying(60) |
    keywordc | character varying(60) |
    clientname | character varying(60) |
    designyear | date |
    filename | character varying(60) |
    primarycolor | character varying(60) |
    secondarycolor | character varying(60) |
    file | bytea |
    topic | character varying(60) |

    However, when my servlet calls:

    select max( photo_id ) from images

    it gets this error:

    SQL EXCEPTION!: The column name photo_id not found.
    SQL EXCEPTION!: null
    SQL EXCEPTION!: 0

    The weird thing is, that same " select max" statement works just fine when typed into the command-line client. Any idea what I'm doing wrong? Or more to the point, know of any other way I can select that last id inserted?

    Thanks so much -- Matt
    However, when my servlet calls:

    select max( photo_id ) from images

    it gets this error:

    SQL EXCEPTION!: The column name photo_id not found.
    SQL EXCEPTION!: null
    SQL EXCEPTION!: 0

    Try:
    select max(photo_id) as photo_id from images

  4. #4
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    Originally posted by Edje
    " photo_id | integer | not null default nextval('photo_seq'::text)"

    Erm shouldnt u create a sequence called:

    create sequence photo_id_seq;

    i assume ur sequence now was created with:
    create sequence photo_seq;

    the column photo doesnt excists in ur table and thus cant be incremented.

    I could be wrong tho, because i have no idea what the ::text part does.
    If i had to create the table i'd would do the following:

    create sequnce photo_id_seq;

    CREATE TABLE <tablenaam>
    (
    photo_id int primary key not null,
    .....)

    and with an insert i would do a simple:
    insert into <tablename> values (nextval('photo_id'),<rest of the values>

    But then again, im a dba noobie.

    -Ed
    phone_seq as a sequence would work. By convertion, when postgres, creates a sequence for you it'll use the name of the field you are created the sequence for and append _seq.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •