Thread: auto generated keys

Would I be right in assuming the calls relating to retrieving auto generated
keys are non-trivial to implement?

I have a somewhat klunky hack in an application which immediately after
inserting a row into a table with a "serial" for the primary key I do a
select based on values I know to be unique (at least for the table in
question).  I also thought about managing the sequence numbers
programmatically but the other method worked out ok.

So
    a) Any ideas about the timeframe for auto generated key retrieval?

    b) Is there a "recommended" method for retrieving auto generated keys
with PostgreSQL and JDBC?

paul

Paul,

Yes, your assumption is correct.

There are two ways you can deal with this in jdbc

1) get the serial first, and then insert it. ie select nextval('
serialcol_seq'); insert into foo (serialcol,...) values( result from
nextval,...)

2) do an insert and get the row oid back and do a select from that.

long insertedOID = ((PGStatement)st).getLastOID()

Dave



On Wed, 2003-03-19 at 03:35, Paul Sorenson wrote:
> Would I be right in assuming the calls relating to retrieving auto generated
> keys are non-trivial to implement?
>
> I have a somewhat klunky hack in an application which immediately after
> inserting a row into a table with a "serial" for the primary key I do a
> select based on values I know to be unique (at least for the table in
> question).  I also thought about managing the sequence numbers
> programmatically but the other method worked out ok.
>
> So
>     a) Any ideas about the timeframe for auto generated key retrieval?
>
>     b) Is there a "recommended" method for retrieving auto generated keys
> with PostgreSQL and JDBC?
>
> paul
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave@micro-automation.net>

"Paul Sorenson" <pauls@classware.com.au> writes:
> I have a somewhat klunky hack in an application which immediately after
> inserting a row into a table with a "serial" for the primary key I do a
> select based on values I know to be unique (at least for the table in
> question).

You can use currval() to read the assigned sequence value after the
insertion.  The other common approach is to select the nextval()
explicitly and then insert it into the new row, instead of allowing
nextval() to be invoked by default.  See the FAQ ...

            regards, tom lane