Get last insert id of Postgresql

Question

As per question:

they have stated that the best way to retrieve the last insert id is:

INSERT INTO ....
RETURNING id;

I want to create a wrapper class for Postgresql so that when i run, for example:

$id = $db_wrapper->insert("INSERT...");

that it will always return the last id inserted into variable $id. I do not know the auto_inc column name for queries that will be passed to the insert() function and I would prefer not to have to add "RETURNING id" to the end of all my insert queries - any ideas on how to achieve this?

One table can use multiple sequences, so which one do you want if you don't know the column name? — Frank Heikens, Mar 14 '15 at 07:26
note that `lastval()` was returning an error for me in postgres 8 on php 7 pdo. `returning id` works. see [accepted solution](https://stackoverflow.com/questions/29035929/get-last-insert-id-of-postgresql#answer-44560436) — WEBjuju, Aug 27 '20 at 18:53

score 13 · Answer 1 · answered Jun 15 '17 at 06:44

13

INSERT with RETURNING

INSERT INTO employee (lastname,firstname) VALUES ('Jain', 'Manish') RETURNING id;

I Prefer this because of thousands of traffic at a time on server might get wrong last curval or LASTVAL(); Althogh it says this both functions are concurrent and work with individual session but i think the

RETURNING id

is better and secure.

Pros: This is very basic and realistic way to get last inserted id, no problem found till the direct entry of 1050 Entry at a time. ;) bit load testing.

Cons: Almost none, you might need to modify the way you call your INSERT statement (in the worst case, perhaps your API or DB layer does not expect an INSERT to return a value); it's not standard SQL (who cares);

answered Jun 15 '17 at 06:44

manish1706

1,571
24
22

1

tricky but nice, worked in postgres 8 with php 7 and pdo connection. right after `$sth->execute();` i grabbed the result with `$row = $sth->fetch(\PDO::FETCH_ASSOC);` as if it had been a `SELECT` statement. so nice, so simple. – WEBjuju Aug 27 '20 at 18:52
RETURNING was introduced in [version 8.2](https://www.postgresql.org/docs/8.2/sql-insert.html); it's absent in [8.1](https://www.postgresql.org/docs/8.1/sql-insert.html) – MM. Jul 01 '21 at 15:22

score 6 · Accepted Answer · edited Jan 18 '23 at 01:28

PostgreSQL will (by default) create a sequence called 'user_id_seq'. for example if your table name is user. then it is user_id_seq

You can then do something like:

$strTable = "user";
$last_insert_id = $objPDO->lastInsertId("{$strTable}_id_seq");

See for multiuser-safe methods http://www.postgresql.org/docs/9.0/interactive/functions-sequence.html

See other ways mysql_insert_id alternative for postgresql

EDIT: as per comment of Eli

//if your table name in model is **user**
$strTable = "user";
$model = new User(); 

// do your stuff
$model->save();

$last_insert_id = $model->lastInsertId("{$strTable}_id_seq");

OR

If your model is called Model, and has a property called id (aka, the PK of the table), then you can acces this way:

//...
$model = new Model();
// do your stuff....
$model->save();

$the_id = $model->id;

PostgreSQL only creates a sequence if you ask for a sequence: CREATE SEQUENCE or using the SERIAL datatype. — Frank Heikens, Mar 14 '15 at 07:24

score -3 · Answer 3 · answered Apr 09 '16 at 09:13

-3

$po_curr_id = PurchaseOrder::select('purchase_orders')->max('id');

answered Apr 09 '16 at 09:13

Eli

1,256
4
29
59

Get last insert id of Postgresql

3 Answers3

Linked