lastInsertId does not work in Postgresql

Question

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.

Possible duplicate of [Get last insert id after a prepared insert with PDO](http://stackoverflow.com/questions/5057954/get-last-insert-id-after-a-prepared-insert-with-pdo) — Alastair Irvine, Aug 04 '16 at 07:34

score 18 · Accepted Answer · edited Feb 24 '16 at 02:01

18

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');

edited Feb 24 '16 at 02:01

Matt S

14,976
6
57
76

answered May 08 '12 at 04:40

mu is too short

426,620
70
833
800

5

doesn't work that way. PGSQL "requires" a parameter to indicate the sequence object. If you leave it blank, nothing is returned. – Michael May 08 '12 at 04:54
3

@Michael: Then you need to know the name of the sequence. The default would be `t_c_seq` where `t` is the table name and `c` 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. – mu is too short May 08 '12 at 04:59
table_column_seq, this is it! Thanks you! – Michael May 08 '12 at 05:15
1

@Michael: You could also look at [`INSERT ... RETURNING id`](http://www.postgresql.org/docs/current/static/sql-insert.html) and bypass `lastInsertId` completely. – mu is too short May 08 '12 at 05:35

score 3 · Answer 2 · edited May 23 '17 at 12:25

3

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');

edited May 23 '17 at 12:25

Community

1
1

answered Feb 16 '16 at 19:14

John F

429
6
5

score 1 · Answer 3 · edited Apr 22 '16 at 18:09

1

So the sequence name will be ingredients_id_seq,

$db->lastInsertId('ingredients_id_seq');

This format actually solved my issues!!!

edited Apr 22 '16 at 18:09

Hongbin Wang

1,186
2
14
34

answered Apr 22 '16 at 16:56

that_developer

319
2
9

you can also use $db->nextval(); – that_developer Jun 28 '16 at 20:22

score -1 · Answer 4 · edited Oct 02 '15 at 09:56

-1

Use sequence name instead column name

$db->lastInsertId('columnName');

edited Oct 02 '15 at 09:56

benka

4,732
35
47
58

answered Oct 02 '15 at 09:34

Wojciech Legierski

51
1
3

lastInsertId does not work in Postgresql

4 Answers4

Linked

Related