PHP Postgres: Get Last Insert ID

Question

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?

Cuthbert

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. – Neoterize

Answer 1

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.

Quarantine

Thanks very much for this answer! Exactly what I needed, halves the number of queries I need to make for certain operations. – Slobbery

Thanks! BTW, if needed, you can return multiple values - RETURNING * will return the entire inserted row. – Separative

What if you don't know the name of the id field? – Hallowmas

@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. – Quarantine

Answer 2

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

Mis

I'm so glad to see that sequences are now ANSI -- AUTOINCREMENT/IDENTITY are too limiting. – Algolagnia