By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,179 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,179 IT Pros & Developers. It's quick & easy.

Getting last inserted SERIAL

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/s...-sequence.html

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

Nov 11 '05 #2

P: n/a

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
pg***@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

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

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.