Re: Getting back the primary key after INSERT

Scott,

> I have a table with an automatically incrementing key. I'm inserting
> things into the table via an INSERT statement from JDBC. Once I do
> the
> INSERT, I want to know what new number was assigned by the database
> to
> the row that I just inserted. Any ideas?

I can't help you with the JDBC. But I can give you the SQL commands.

First, you need to locate the name of the sequence created for your
SERIAL primary key. If the "table1" has an "id" field, the name will
probably be table1_id_seq.

Then, you need to do this *inside a transaction or JDBC session*:

INSERT ...
SELECT CURRVAL('name_of_primary_key_sequence');

This will return the "current" value of the sequence; in other words,
the primary key of the row you just inserted.

Unfortunately, the online docs do not cover the sequence functions very
well (CURRVAL, NEXTVAL, and SETVAL). It's well documented in the books,
such as Bruce Momjian's book.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco