Getting last inserted SERIAL

[mgarriss]

Given this table:

CREATE TABLE test ( id SERIAL, example TEXT );

An implicit sequence is created as show in this message:

NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for
SERIAL column 'test.id'

How do I retrieve the last 'id' that was inserted? I have a process
that does an insert and then needs the value of the id column of the row
it just inserted so that that row can be used later in processing.
First thought is "SELECT CURRVAL('test_i d_seq');" but this assumes that
there is only one connection inserting into this table, bad assumption.
Next idea is the make 'id' an INT4 and set it explicitly with a value
that I select from an explicitly created sequence. This method seems a
bit inelegant. Any ideas?

TIA
Michael Garriss
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dennis Björklund]

On Sun, 31 Aug 2003, mgarriss wrote:

First thought is "SELECT CURRVAL('test_i d_seq');" but this assumes that
there is only one connection inserting into this table, bad assumption.

That is what you should use, and it works for concurrent sessions. It's
all described in the manual:

http://www.postgresql.org/docs/7.3/s...-sequence.html

--
/Dennis
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruce Momjian]

Read the FAQ. Your currval assumption is wrong.

---------------------------------------------------------------------------

mgarriss wrote:

Given this table:

CREATE TABLE test ( id SERIAL, example TEXT );

An implicit sequence is created as show in this message:

NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for
SERIAL column 'test.id'

How do I retrieve the last 'id' that was inserted? I have a process
that does an insert and then needs the value of the id column of the row
it just inserted so that that row can be used later in processing.
First thought is "SELECT CURRVAL('test_i d_seq');" but this assumes that
there is only one connection inserting into this table, bad assumption.
Next idea is the make 'id' an INT4 and set it explicitly with a value
that I select from an explicitly created sequence. This method seems a
bit inelegant. Any ideas?

TIA
Michael Garriss
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.ph a.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend