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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco