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?

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
– jordani, Commented Jun 27, 2011 at 0:01

Eelke · Accepted Answer · 2011-06-27 05:16:48Z

33

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.

answered Jun 27, 2011 at 5:16

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PROGRAM_IX Over a year ago

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

SamGoody Over a year ago

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

JoeTidee Over a year ago

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

Eelke Over a year ago

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

Sjoerd · Accepted Answer · 2011-06-26 18:14:15Z

6

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

answered Jun 26, 2011 at 18:14

Sjoerd

75.9k16 gold badges140 silver badges180 bronze badges

1 Comment

OMG Ponies Over a year ago

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

Collectives™ on Stack Overflow

PHP Postgres: Get Last Insert ID

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related