* * *

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

kapibara

  • Sr. Member
  • ****
  • Posts: 479
PostGres - Getting last inserted ID
« on: March 10, 2017, 10:16:42 pm »
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
« Last Edit: February 17, 2018, 04:37:38 am by kapibara »
Lazarus trunk / fpc 3.0.4 / Debian Stretch 64-bit

goodname

  • Sr. Member
  • ****
  • Posts: 294
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: 479
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.4 / Debian Stretch 64-bit

JD

  • Hero Member
  • *****
  • Posts: 1703
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.8/FPC 3.0.4, NewPascal, Delphi
Linux Mint Cinnamon  - Lazarus 1.8/FPC 3.0.4, NewPascal

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

Abelisto

  • Jr. Member
  • **
  • Posts: 67
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