lastInsertId does not work in Postgresql
Solution 1
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');
Solution 2
I ran into this issue today, lastInsertId() was only returning false. Found the answer that solved my issue on a different thread: https://stackoverflow.com/a/31638196/1477123
CREATE TABLE ingredients (
id SERIAL PRIMARY KEY,
name varchar(255) NOT NULL,
);
So the sequence name will be ingredients_id_seq
$db->lastInsertId('ingredients_id_seq');
Solution 3
So the sequence name will be ingredients_id_seq
,
$db->lastInsertId('ingredients_id_seq');
This format actually solved my issues!!!
Comments
-
Michael almost 2 years
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.
-
Michael almost 12 yearsdoesn't work that way. PGSQL "requires" a parameter to indicate the sequence object. If you leave it blank, nothing is returned.
-
mu is too short almost 12 years@Michael: Then you need to know the name of the sequence. The default would be
t_c_seq
wheret
is the table name andc
is the column name. This requirement is rather bizarre though. I don't have all the PHP/PostgreSQL set up ATM so I can't verify any of this myself. -
Michael almost 12 yearstable_column_seq, this is it! Thanks you!
-
mu is too short almost 12 years@Michael: You could also look at
INSERT ... RETURNING id
and bypasslastInsertId
completely. -
that_developer almost 8 yearsyou can also use $db->nextval();