mysql_insert_id alternative for postgresql

Question

is there an alternative for mysql_insert_id() php function for PostgreSQL? Most of the frameworks are solving the problem partially by finding the current value of the sequence used in the ID. However, there are times that the primary key is not a serial column....

Answer 1

Check out the RETURNING optional clause for an INSERT statement. (Link to official PostgreSQL documentation)

But basically, you do:

INSERT INTO table (col1, col2) VALUES (1, 2) RETURNING pkey_col

and the INSERT statement itself returns the id (or whatever expression you specify) of the affected row.

Answer 2

From php.net:

$res=pg_query("SELECT nextval('foo_key_seq') as key");
$row=pg_fetch_array($res, 0);
$key=$row['key'];
// now we have the serial value in $key, let's do the insert
pg_query("INSERT INTO foo (key, foo) VALUES ($key, 'blah blah')");

This should always provide unique key, because key retrieved from database will be never retrieved again.

Answer 3

(they were saying about adding annotation to reply but i don't seem to find it... sorry for misusing the system for discussion...)

@Vertigo erm... solution to retrieve the insert value is basically well solved if the primary key is a serial column. However, I am having this problem where I need to get the insert ID after issuing an insert statement to a table that doesn't have a serial PK...

I wasn't very specific in my question and not sure whether it is an good idea to edit it (as it will lead to another answer later). But in case you want to know, there is another discussion which i started in Kohana Forum http://forum.kohanaphp.com/comments.php?DiscussionID=1145 if you need further information

Answer 4

From the PostgreSQL point of view, in pseudo-code:

• $insert_id = INSERT...RETURNING foo_id;-- only works for PostgreSQL >= 8.2.

• INSERT...; $insert_id = SELECT lastval(); -- works for PostgreSQL >= 8.1

• $insert_id = SELECT nextval('foo_seq'); INSERT INTO table (foo...) values ($insert_id...) for older PostgreSQL (and newer PostgreSQL)

pg_last_oid() only works where you have OIDs. OIDs have been off by default since PostgreSQL 8.1.

So, depending on which PostgreSQL version you have, you should pick one of the above method. Ideally, of course, use a database abstraction library which abstracts away the above. Otherwise, in low level code, it looks like:

Method one: INSERT... RETURNING

// yes, we're not using pg_insert()
$result = pg_query($db, "INSERT INTO foo (bar) VALUES (123) RETURNING foo_id");
$insert_row = pg_fetch_row($result);
$insert_id = $insert_row[0];

Method two: INSERT; lastval()

$result = pg_execute($db, "INSERT INTO foo (bar) values (123);");
$insert_query = pg_query("SELECT lastval();");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];

Method three: nextval(); INSERT

$insert_query = pg_query($db, "SELECT nextval('foo_seq');");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];
$result = pg_execute($db, "INSERT INTO foo (foo_id, bar) VALUES ($insert_id, 123);");

The safest bet would be the third method, but it's unwieldy. The cleanest is the first, but you'd need to run a recent PostgreSQL. Most db abstraction libraries don't yet use the first method though.