Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 11 down vote favorite
3

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

share|improve this question
add comment

5 Answers

up vote 28 down vote accepted

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.

share|improve this answer
 
Great answer, I switched from the third method to the first for speed reasons. I am using python and psycopg2 and executing only one statement for bulk data inserting is simply a faster way. Would like to know if the third is really any safer than the first? I would think that the first and third are equally safe and the second has a race condition. –  Ross Dec 12 '12 at 4:25
add comment
up vote 3 down vote

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.

share|improve this answer
add comment
up vote 2 down vote

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.

share|improve this answer
add comment
up vote 1 down vote

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.

share|improve this answer
 
yea, it is already mentioned in @angch's answer. Note that this is not usable for other sql-dialects (sql-server, mysql etc.) though –  Jeffrey04 May 4 '11 at 3:18
add comment
up vote 0 down vote

(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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.