PostGres - Getting last inserted ID

[kapibara]

Postgres has a feature that gives back the ID of an inserted record directly, without having to do a select later: INSERT RETURNING. Added an example to the WIKI.

http://wiki.lazarus.freepascal.org/postgres#How_To

[goodname]

Thankyou kapibara. The RETURNING clause was added to SQLdb a long time ago and it is finally documented in the wiki. The documentation is not really complete. RETURNING in PostgreSQL is more flexible than most database engines. It works with INSERT, UPDATE and DELETE statements and can be any list of fields that would be found in a SELECT field list.

Code: SQL  [Select][+][-]

1. INSERT INTO films (filmname) VALUES ('val') RETURNING id;  -- Returns id's for newly created rows.
2. INSERT INTO films (filmname) VALUES ('val') RETURNING id, kind;  -- Returns id and kind fields in newly created rows.
3. INSERT INTO films (filmname) VALUES ('val') RETURNING *; -- Returns all fields in newly created rows.
4.  
5. UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING id; -- Returns id's of updated rows.
6. UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING id, filmname; -- Returns id and film names of updated rows.
7. UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING *; -- Returns all fields of updated rows.
8.  
9. DELETE FROM films RETURNING id; --Returns id's of deleted rows.
10. DELETE FROM films RETURNING id, filmname; --Returns id's and film names of deleted rows.
11. DELETE FROM films RETURNING *;  -- Returns all fields of deleted rows.

Edit: Fixed syntax errors in insert statements.

[kapibara]

Great example, goodname, updated wiki.

[JD]

This is one of the many reasons why PostgreSQL has become my favourite database. It has sooo many nice features that help save time and effort.

JD

[Abelisto]

can be any list of fields that would be found in a SELECT field list.

BTW not only fields but also constants and expressions:

Code: SQL  [Select][+][-]

1. INSERT INTO films (filmname) VALUE 'val'
2. RETURNING id, 'a' AS a, id*2 AS doubled_id, CASE WHEN id > 100 THEN 'a' ELSE 'b' END AS foo;