Postgres/PHP - What is the standard way to retrieve the ID of an inserted row?

Question

I have searched on Google and read a couple of articles on how different people approach this problem, but I was wondering what the standard way of solving it is and also, which one will work best for my situation.

I have an AJAX page that creates a new question and I need to know how to retrieve the ID from the insert query within the same php file, on the next line.

It looks something like this:

$r = pg_query("INSERT INTO questions (audit_id, type_id, order) VALUES (1,1,1)");
// Fetch ID from $r here...

I have seen the mysql_insert_id() function for MySQL and heard that pg_last_oid() is similar for PostgreSQL, but the documentation claims that it is deprecated and will be removed soon. I have also seen the use of CURRVAL('my_sequence_table.id') but I'm not sure if this will work with the AJAX since that might raise a race condition.

Can somebody please tell me the standard PHP/PostgreSQL way to solve this problem? I would greatly appreciate any comments.

P.S. I miss Ruby on Rails!

Answer 1

MySQL has the notion of autoincrement fields, PostgreSQL has the notion of sequences. A Postgres sequence is a named database object whose value can be increased. Please see this FAQ.

Answer 2

Probably your best bet is to use INSERT INTO questions ... VALUES (1,1,1) RETURNING audit_id, as that will give you the right value whether you plug the value manually or through a sequence.

Note that the currval() trick should certainly work if you get the same session -- currval() is guaranteed to return the same value that the sequence delivered to your session, regardless of what other concurrent sessions are doing. It could only cause a problem if you have a connection pooler that somehow uses a different connection for the first query than the second query, but it would be quite broken a pooler if it did that. I know of no pooler that does things that way.

Update: See the pg_get_serial_sequence() function, which takes a table and column name and returns the associated sequence name. It is more practical to use than hardcoding the sequence name in your code.

Answer 3

I would recommend using the CURRVAL('my_sequence_table.id') option.

It shouldn't cause a race condition for you as it will return the latest value for the current session and these will be two consecutive statements in the same session.