Thread: Getting back the primary key after INSERT

I'm pretty new with JDBC and Postgres. Here's my question:

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?

--Scott

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

Scott,
I am pretty new at this also but here is what I used.
After adding a record to table persons I need to get the latest
value from its sequence file, persons_id_seq, and use it to
link a new record in table addresses.

/* Get the ID value for the volunteer just added to persons table
We will use this as the index for the addresses table  */
 $personidsql = "select last_value from persons_id_seq";
 $result = pg_Exec($GLOBALS["localhost"], $personidsql);
 $myrow = pg_fetch_row ($result,0);    echo "person id",$myrow[0],"<BR>";

Now key value is in $myrow[0].


/* Add Mailing Address */
if ($maddress1 != "") {
   $address_type = "mailing";
   $sqladdress = "insert into addresses
    (person_id,address_type, address1, address2, city, state_code,
province,
     country_code, postalcode, remarks)
    values
    ('$myrow[0]','$address_type','$maddress1','$maddress2','$mcity',
    '$mstate_code','$mprovince','$mcountry_code',
'$mpostalcode','$remarks')";


Cheers, Tom

On 22 Oct 2001, at 13:40, Scott W. Hill wrote:

> I'm pretty new with JDBC and Postgres. Here's my question:
>
> 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?
>
> --Scott
Tom Hodges, hodges@xprt.net or tom_hodges@yahoo.com
ICQ 10149621, YahooMessenger tom_hodges
Mail: 14314 SW Allen Blvd, #317; Beaverton OR 97005 USA