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

1
22
8/1/2016 4:34:24 AM

Accepted Answer

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.

43
6/9/2014 9:49:38 AM

You also can use:

$result = pg_query($db, "INSERT INTO foo (bar) VALUES (123) RETURNING foo_id");
$insert_row = pg_fetch_result($result, 0, 'foo_id');

You have to specify in pg_fetch_result the number of the row and the name of the field that you are looking for, this is a more precise way to get the data that you need, but I don't know if this has some penalty in the performance of the query. Remember that this method is for PostgreSQL versions 8.2 and up.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon