Getting ID of last-inserted row

In response to David :

Hi there.

I never found an adequate (simple and efficient) method for getting
the primary key ID of the just-inserted row, and usually used
transactions and "select last value, ordered by id"-type queries to
get the last id value, or other ugly logic.

use currval() instead, see
http://www.postgresql.org/docs/current/static/functions-sequence.html
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On 20/05/2009 11:17, A. Kretschmer wrote:

In response to David :

Hi there.

I never found an adequate (simple and efficient) method for getting
the primary key ID of the just-inserted row, and usually used
transactions and "select last value, ordered by id"-type queries to
get the last id value, or other ugly logic.

use currval() instead, see
http://www.postgresql.org/docs/current/static/functions-sequence.html

Also, you can do insert....returning... (as of version 8.2, I think):

INSERT INTO clients (id, name)
VALUES (nextval('clients_id_seq'), 'John Smith')
RETURNING id;

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

I never found an adequate (simple and efficient) method for getting
the primary key ID of the just-inserted row, and usually used
transactions and "select last value, ordered by id"-type queries to
get the last id value, or other ugly logic.

use currval() instead, see
http://www.postgresql.org/docs/current/static/functions-sequence.html
--

I think I researched that before also, but I wasn't sure at the time
how safe it was against race conditions. Although I see now (reading
the docs again) that it is tracked for different sessions so it should
be safe. There might also be issues (for instance, where you are using
a complex database-handling library), where you can't always guarantee
that your currval() call is made at the right time. But most of the
time it should be fine. Thanks for the reminder.

Also, you can do insert....returning... (as of version 8.2, I think):

INSERT INTO clients (id, name)
 VALUES (nextval('clients_id_seq'), 'John Smith')
 RETURNING id;

Thanks. I think I saw that too, not too long ago, but forgot about it.
Some of the PostgreSQL services I use are on older versions, so I need
to use older syntax. But, this will be useful when the db version is
guaranteed to be recent. Thanks also for your reminder.

On Wed, May 20, 2009 at 4:35 AM, David <wizzardx@gmail.com> wrote:

I never found an adequate (simple and efficient) method for getting
the primary key ID of the just-inserted row, and usually used
transactions and "select last value, ordered by id"-type queries to
get the last id value, or other ugly logic.

use currval() instead, see
http://www.postgresql.org/docs/current/static/functions-sequence.html
--

I think I researched that before also, but I wasn't sure at the time
how safe it was against race conditions. Although I see now (reading
the docs again) that it is tracked for different sessions so it should
be safe.

It is.

Also, you can do insert....returning... (as of version 8.2, I think):

INSERT INTO clients (id, name)
 VALUES (nextval('clients_id_seq'), 'John Smith')
 RETURNING id;

Thanks. I think I saw that too, not too long ago, but forgot about it.
Some of the PostgreSQL services I use are on older versions, so I need
to use older syntax. But, this will be useful when the db version is
guaranteed to be recent. Thanks also for your reminder.

The one thing returning makes easy is getting the return id of multiple records.

insert into tablea values (val),(val),(val),(val) returning id;

will return a set of 4 different ids, not just one. And if there's a
gap in the sequence you'll not get the wrong numbers.