Thread: Getting ID of last-inserted row
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. That was until I found how SQLalchemy[1] handles it for PostgreSQL. What they do is: 1) First, get the next value from the sequence, eg: SELECT nextval('clients_id_seq'); 2) Then, run an insert statement, where the retrieved value is explicitly given, rather than automatically assigned, eg: INSERT INTO clients (id, name) VALUES (12345, 'John Smith'); (Where 12345 is the id retrieved from the previous query). I wanted to add this info to the wiki[2], but there doesn't seem to be a way to sign up. Anyway, I thought that other people might find this info useful. David. [1] http://www.sqlalchemy.org/ [2] http://wiki.postgresql.org/wiki/Main_Page
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.