* * *

Author Topic: PostGres - Getting last inserted ID  (Read 361 times)

kapibara

  • Sr. Member
  • ****
  • Posts: 430
PostGres - Getting last inserted ID
« on: March 10, 2017, 10:16:42 pm »
Postgres has a feature that gives the ID back directly on insert, without having to do a select later: INSERT RETURNING. Added an example to the WIKI.

http://wiki.lazarus.freepascal.org/postgres#How_To
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

goodname

  • Sr. Member
  • ****
  • Posts: 282
Re: PostGres - Getting last inserted ID
« Reply #1 on: March 11, 2017, 02:28:58 pm »
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.
« Last Edit: March 13, 2017, 04:40:30 pm by goodname »

kapibara

  • Sr. Member
  • ****
  • Posts: 430
Re: PostGres - Getting last inserted ID
« Reply #2 on: March 12, 2017, 02:45:14 am »
Great example, goodname, updated wiki.
« Last Edit: March 12, 2017, 04:44:14 am by kapibara »
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

JD

  • Hero Member
  • *****
  • Posts: 1520
Re: PostGres - Getting last inserted ID
« Reply #3 on: March 13, 2017, 10:28:34 am »
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
Windows (10, 7) - Lazarus 1.6.2/FPC 3.0 (also Delphi occasionally)
Linux Mint Cinnamon  - Lazarus 1.4/FPC 2.6.4
Indy 10.6 series; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB

Abelisto

  • Jr. Member
  • **
  • Posts: 58
Re: PostGres - Getting last inserted ID
« Reply #4 on: March 13, 2017, 02:06:41 pm »
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;
OS: Linux Mint + MATE, Compiler: FPC trunk (yes, I am risky!), IDE: Lazarus trunk

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus