PHP Postgres: Get Last Insert ID

php sql postgresql sequence last-insert-id
Jonah picture Jonah · Jun 26, 2011 · Viewed 21.3k times · Source

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?

Answer

Eelke picture Eelke · Jun 27, 2011

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.

Related questions

Cannot simply use PostgreSQL table name ("relation does not exist")

I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could …

Read More
Best way to get result count before LIMIT was applied

When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls. Currently I do that by running the query twice, once wrapped in a count() to …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More