Thread: Getting last inserted SERIAL

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_id_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

On Sun, 31 Aug 2003, mgarriss wrote:

> First thought is "SELECT CURRVAL('test_id_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/static/functions-sequence.html

--
/Dennis

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_id_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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073