PostGres - Getting last inserted ID
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2017-03-10 21:16

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 · 2017-03-11 13:28

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
films (filmname) VALUES ('val') RETURNING id;  -- Returns id's for
newly created rows.INSERT INTO films (filmname) VALUES ('val')
RETURNING id, kind;  -- Returns id and kind fields in newly created
rows.INSERT INTO films (filmname) VALUES ('val') RETURNING *; --
Returns all fields in newly created rows. UPDATE films SET kind =
'Dramatic' WHERE kind = 'Drama' RETURNING id; -- Returns id's of
updated rows.UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'
RETURNING id, filmname; -- Returns id and film names of updated
rows.UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING
*; -- Returns all fields of updated rows. DELETE FROM films RETURNING
id; --Returns id's of deleted rows.DELETE FROM films RETURNING id,
filmname; --Returns id's and film names of deleted rows.DELETE FROM
films RETURNING *;  -- Returns all fields of deleted rows.Edit: Fixed
syntax errors in insert statements.

----------------------------------------------------------------------
fpcoder · 2017-03-12 01:45

Great example, goodname, updated wiki.

----------------------------------------------------------------------
JD · 2017-03-13 09:28

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 · 2017-03-13 13:06

Quote from: goodname on March 11, 2017, 02:28:58 pmcan 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
films (filmname) VALUE 'val'RETURNING id, 'a' AS a, id*2 AS
doubled_id, CASE WHEN id > 100 THEN 'a' ELSE 'b' END AS foo;