15

I found a couple of other questions on this topic. This one...

mysql_insert_id alternative for postgresql

...and the manual seem to indicate that you can call lastval() any time and it will work as expected. But this one...

Postgresql and PHP: is the currval a efficent way to retrieve the last row inserted id, in a multiuser application?

...seems to state that it has to be within a transaction. So my question is this: can I just wait as long as I like before querying for lastval() (without a transaction)? And is that reliable in the face of many concurrent connections?

Community
  • 1
  • 1
Jonah
  • 9,991
  • 5
  • 45
  • 79
  • Sequences depends on session not on transaction. But it is not good to wait and execute other queries meanwhile, you may receive lastval of other query. – jordani Jun 27 '11 at 00:01

2 Answers2

32

INSERT, UPDATE and DELETE in PostgreSQL have a RETURNING clause which means you can do:

INSERT INTO ....
RETURNING id;

Then the query will return the value it inserted for id for each row inserted. Saves a roundtrip to the server.

Eelke
  • 20,897
  • 4
  • 50
  • 76
  • Thanks very much for this answer! Exactly what I needed, halves the number of queries I need to make for certain operations. – PROGRAM_IX Apr 17 '12 at 13:31
  • 3
    Thanks! BTW, if needed, you can return multiple values - RETURNING * will return the entire inserted row. – SamGoody May 19 '14 at 12:10
  • What if you don't know the name of the id field? – JoeTidee Mar 13 '15 at 01:04
  • @JoePrivett, in normal applications that is not very realistic. Do you have a concrete case? If yes it would be better to post your own question where you explain your problem in more detail. – Eelke Mar 13 '15 at 11:30
6

Yes, the sequence functions provide multiuser-safe methods for obtaining successive sequence values from sequence objects.

Sjoerd
  • 74,049
  • 16
  • 131
  • 175