PostgreSQL function for last inserted ID

Question

In PostgreSQL  how do I get the last id inserted into a table   In MS SQL there is SCOPE IDENTITY     Please do not advise me to use something like this   select max id  from table

User · Answer

I had this issue with Java and Postgres  I fixed it by updating a new Connector-J version   postgresql-9 2-1002 jdbc4 jar  https   jdbc postgresql org download html   Version 42 2 12  https   jdbc postgresql org download postgresql-42 2 12 jar

User · Answer

See the below example  CREATE TABLE users       -- make the  id  column a primary key  this also creates     -- a UNIQUE constraint and a b -tree index on the column     id    SERIAL PRIMARY KEY      name  TEXT      age   INT4     INSERT INTO users  name  age  VALUES   Mozart   20     Then for getting last inserted id  use this for table  user    seq column name  id   SELECT currval pg get serial sequence  users    id

User · Answer

Postgres has an inbuilt mechanism for the same  which in the same query returns the id or whatever you want the query to return  here is an example  Consider you have a table created which has 2 columns column1 and column2 and you want column1 to be returned after every insert     create table users table id serial not null primary key  name character varying   CREATE TABLE  insert into users table name  VALUES   Jon Snow   RETURNING id    id  ----   1  1 row     insert into users table name  VALUES   Arya Stark   RETURNING id    id  ----   2  1 row

User · Answer

Try this   select nextval  my seq name        Returns next value   If this return 1  or whatever is the start value for your sequence   then reset the sequence back to the original value  passing the false flag   select setval  my seq name   1  false     Otherwise    select setval  my seq name   nextValue - 1  true     This will restore the sequence value to the original state and  setval  will return with the sequence value you are looking for

User · Answer

See the RETURNING clause of the INSERT statement  Basically  the INSERT doubles as a query and gives you back the value that was inserted

User · Answer

You can use RETURNING id after insert query  INSERT INTO distributors  id  name  VALUES  DEFAULT   ALI   RETURNING id   and result   id  ----   1   In the above example id is auto-increment filed

User · Answer

For the ones who need to get the all data record  you can add  returning     to the end of your query to get the all object including the id

User · Answer

Leonbloy s answer is quite complete  I would only add the special case in which one needs to get the last inserted value from within a PL pgSQL function where OPTION 3 doesn t fit exactly   For example  if we have the following tables   CREATE TABLE person     id serial     lastname character varying  50      firstname character varying  50      CONSTRAINT person pk PRIMARY KEY  id      CREATE TABLE client       id integer     CONSTRAINT client pk PRIMARY KEY  id      CONSTRAINT fk client person FOREIGN KEY  id         REFERENCES person  id  MATCH SIMPLE      If we need to insert a client record we must refer to a person record  But let s say we want to devise a PL pgSQL function that inserts a new record into client but also takes care of inserting the new person record  For that  we must use a slight variation of leonbloy s OPTION 3   INSERT INTO person lastname  firstname   VALUES  lastn  firstn   RETURNING id INTO  new variable     Note that there are two INTO clauses  Therefore  the PL pgSQL function would be defined like   CREATE OR REPLACE FUNCTION new client lastn character varying  firstn character varying    RETURNS integer AS  BODY  DECLARE    v id integer  BEGIN    -- Inserts the new person record and retrieves the last inserted id    INSERT INTO person lastname  firstname     VALUES  lastn  firstn     RETURNING id INTO v id      -- Inserts the new client and references the inserted person    INSERT INTO client id  VALUES  v id       -- Return the new id so we can use it in a select clause or return the new id into the user application     RETURN v id  END   BODY    LANGUAGE plpgsql VOLATILE    Now we can insert the new data using    SELECT new client  Smith    John      or   SELECT   FROM new client  Smith    John      And we get the newly created id   new client integer ----------          1

User · Answer

SELECT CURRVAL pg get serial sequence  my tbl name   id col name      You need to supply the table name and column name of course   This will be for the current session   connection http   www postgresql org docs 8 3 static functions-sequence html

User · Answer

you can use RETURNING clause in INSERT statement just like the following  wgzhao   create table foo id int name text   CREATE TABLE wgzhao   insert into foo values 1  wgzhao   returning id   id  ----   1  1 row   INSERT 0 1 wgzhao   insert into foo values 3  wgzhao   returning id   id  ----   3  1 row   INSERT 0 1  wgzhao   create table bar id serial name text   CREATE TABLE wgzhao   insert into bar name  values  wgzhao   returning id   id  ----   1  1 row   INSERT 0 1 wgzhao   insert into bar name  values  wgzhao   returning id   id  ----   2  1 row   INSERT 0

User · Answer

Based on  ooZman  s answer above  this seems to work for PostgreSQL v12 when you need to INSERT with the next value of a  quot sequence quot   akin to auto increment  without goofing anything up in your table s  counter s    Note  I haven t tested it in more complex DB cluster configurations though     Psuedo Code  insert next id    return result- gt query  quot  select  setval   quot your id seq quot     select nextval   quot your id seq quot     - 1  true    1   quot

User · Answer

tl dr   goto option 3  INSERT with RETURNING    Recall that in postgresql there is no  id  concept for tables  just sequences  which are typically but not necessarily used as default values for surrogate primary keys  with the SERIAL pseudo-type     If you are interested in getting the id of a newly inserted row  there are several ways     Option 1  CURRVAL  lt sequence name gt        For example     INSERT INTO persons  lastname firstname  VALUES   Smith    John      SELECT currval  persons id seq      The name of the sequence must be known  it s really arbitrary  in this example we assume that the table persons has an id column created with the SERIAL pseudo-type  To avoid relying on this and to feel more clean  you can use instead pg get serial sequence     INSERT INTO persons  lastname firstname  VALUES   Smith    John      SELECT currval pg get serial sequence  persons   id       Caveat  currval   only works after an INSERT  which has executed nextval      in the same session     Option 2  LASTVAL     This is similar to the previous  only that you don t need to specify the sequence name  it looks for the most recent modified sequence  always inside your session  same caveat as above      Both CURRVAL and LASTVAL are totally concurrent safe  The behaviour of sequence in PG is designed so that different session will not interfere  so there is no risk of race conditions  if another session inserts another row between my INSERT and my SELECT  I still get my correct value    However they do have a subtle potential problem  If the database has some TRIGGER  or RULE  that  on insertion into persons table  makes some extra insertions in other tables    then LASTVAL will probably give us the wrong value  The problem can even happen with CURRVAL  if the extra insertions are done intto the same persons table  this is much less usual  but the risk still exists      Option 3   INSERT with RETURNING   INSERT INTO persons  lastname firstname  VALUES   Smith    John   RETURNING id    This is the most clean  efficient and safe way to get the id  It doesn t have any of the risks of the previous    Drawbacks  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   it s available since Postgresql 8 2  Dec 2006        Conclusion  If you can  go for option 3  Elsewhere  prefer 1   Note  all these methods are useless if you intend to get the last inserted id globally  not necessarily by your session   For this  you must resort to SELECT max id  FROM table  of course  this will not read uncommitted inserts from other transactions     Conversely  you should never use SELECT max id  FROM table instead one of the 3 options above  to get the id just generated by your INSERT statement  because  apart from performance  this is not concurrent safe  between your INSERT and your SELECT another session might have inserted another record

[postgresql] PostgreSQL function for last inserted ID

Examples related to postgresql

Examples related to insert

Examples related to lastinsertid