lastInsertId does not work in Postgresql

php postgresql pdo lastinsertid
Michael picture Michael · May 8, 2012 · Viewed 10.5k times · Source

I am using Postgresql, when I want to use PDO to retrieve the latest insertion ID, I got a problem. Here is my code:

$db->lastInsertId('columnName');

The error message says 

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "columnName" does not exist

I guess I have some misunderstanding about "sequence object" stated in the PHP Manual.

Note:

Returns the ID of the last inserted row, or the last value from a sequence object, 
depending on the underlying driver. For example, PDO_PGSQL() requires you to specify the 
name of a sequence object for the name parameter.

Currently, the "columnName" is the string of that auto-incremented attribute. Can anyone point out where I went wrong? Thanks.

Answer

mu is too short picture mu is too short · May 8, 2012

PostgreSQL uses sequences to generate values for serial columns and serial columns are generally what is used for "auto-incrementing" columns in PostgreSQL. Sequences have names and are, in general, independent of any particular table so you could have one sequence generating unique IDs for several different tables; the sequence name is what lastInsertId wants as its argument:

For example, PDO_PGSQL() requires you to specify the name of a sequence object for the name parameter.

The sequence object created by PostgreSQL is automatically named [table]_[column]_seq, So:

$id = $db->lastInsertId('tableName_columnName_seq');

Related questions

How do I enable php to work with postgresql?

<?php try { $dbh = new PDO('pgsql:host=localhost;port=5432;dbname=###;user=###;password=##'); echo "PDO connection object created"; } catch(PDOException $e) { echo $e->getMessage(); } ?> I get the error message "Could Not Load Driver"

Read More
Get last insert id after a prepared insert with PDO

I'm using PHP PDO with PostgreSQL for a new project. Given the following function, how can I return the id of the row just inserted? It doesn't work the way it looks now. function adauga_administrator($detalii) { global $db; $ultima_…

Read More
Setting a connect timeout with PDO

I'm using PDO to get data off a MySQL server. What I noticed is this: if the MySQL server is unavailable, it takes really (relatively) long for this code to return an exception: try { $handle = new PDO($db_type . ':…

Read More