457

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
jkdev
  • 11,360
  • 15
  • 54
  • 77
Anton
  • 9,682
  • 11
  • 38
  • 68
  • 1
    why do you hate the max function? I think it's very simple. Is there any problem such as security? – jeongmin.cha Nov 25 '16 at 07:41
  • 20
    @jeongmin.cha there is problem if in between there are other operations, and more insertions(concurrent operations), means max id has changed, unless and until you take a lock explicitly and don't release it – rohanagarwal Jul 14 '17 at 14:00
  • 1
    If the intended use case is to use the last inserted ID as part of the value of a subsequent insert, see [this question](https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert). – Flux May 21 '19 at 14:42

14 Answers14

806

( 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(<sequence name>);.

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.

leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • 32
    LASTVAL() might be very evil, in case you add a trigger / rule inserting rows on itself into another table. – Kouber Saparev Oct 25 '12 at 15:17
  • 3
    `SELECT max(id)` unfortunately does not do the job either as soon as you start deleting rows. – Simon A. Eugster Nov 02 '12 at 16:44
  • 2
    @leonbloy Unless I missed something, if you have rows with IDs `1,2,3,4,5` and delete the rows 4 and 5, the last inserted ID is still 5, but `max()` returns 3. – Simon A. Eugster Nov 02 '12 at 20:47
  • @SimonA.Eugster Ah, no, by "last inserted id globally" I meant among the currently existing rows - that's what one is typicaly interested in - though I agree that my wording is not very good. – leonbloy Nov 02 '12 at 23:47
  • @leonbloy Ah okay, I was interested in the other one ;) – Simon A. Eugster Nov 04 '12 at 09:47
  • I m using CURRVAL in postgresql 8.2 however its giving me an error Currval not supported. Any work around for this is much appreciated. Thanks. – Nemo Apr 03 '14 at 16:24
  • I just found out that there is problem with LASTVAL() is not just in case of triggers. When I insert something like `insert into some_table (some_column) values (some_function())`, then `some_function()` is apparently executed later than the insert itself, so if it also inserts something, then LASTVAL() will return the id of that. – petersohn Apr 11 '14 at 14:21
  • I would avoid using SELECT MAX() in favor of SELECT last_value FROM person_id_seq. Much faster. – Gustavo Pinsard May 08 '14 at 20:05
  • @GustavoPinsard : SELECT MAX() and SELECT last_value are different things, and can give different results. – leonbloy May 08 '14 at 21:01
  • 24
    A sample of how to use `RETURNING id;` to insert this in another table would be welcome! – Olivier Pons Dec 27 '15 at 21:53
  • Just in case anyone's wondering--[here](https://en.wikipedia.org/wiki/Insert_(SQL)#Retrieving_the_key) is a list of how this is possible in other databases' languages. As the answer (kind of) alludes to, there is no directly SQL-compliant way to get the last-inserted ID – villapx Mar 16 '16 at 18:24
  • 12
    How can I use `RETURNING id` within an SQL script fed to the `psql` command line tool? – amoe Aug 04 '16 at 16:52
  • Option 1 Worked for me like a charm. +1 – AVK Sep 26 '16 at 20:52
  • 2
    LASTVAL() is not evil, it's Triggers that are evil – Davos Nov 13 '17 at 09:42
  • 2
    I know you have a tl;dr but I'm not sure why you ordered your options the way you did, you should move option 3 to option 1 as it's the most practical solution and the one that most users will go with anyways. – Airerr Jul 22 '18 at 04:46
  • 2
    option 3 is the simplest and convenient option. thanks – Eswar May 29 '19 at 13:49
  • How to store the last insert id in a variable in order to reuse it next time? – logbasex Jan 20 '21 at 09:26
  • How to do two inserts and use the returning id of the first insert in the second insert? – ceving Jul 16 '21 at 18:25
  • Where the inserted query returns the returned id...I'm unable to see anything after even insertion of row... – Mobin Al Hassan Sep 02 '21 at 06:50
99

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

kenm
  • 23,127
  • 2
  • 43
  • 62
  • 5
    Works as of version 8.2 and is the best and fastest solution. – Frank Heikens May 31 '10 at 15:04
  • 15
    maybe a quick explanation of how to use said returned id? – Andrew Apr 12 '16 at 20:02
  • 12
    @Andrew, If you **run from psql command line** this: `insert into names (firstname, lastname) values ('john', 'smith') returning id;`, then it simply outputs id just as if you ran `select id from names where id=$lastid` directly. If you want to **save the return into a a variable**, then `insert into names (firstname, lastname) values ('john', 'smith') returning id into other_variable;` If the statement containing the returning is the **last statement in a function**, then the id being `returning`'ed is returned by the function as a whole. – Alexander Bird Jul 16 '16 at 20:23
52

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
Community
  • 1
  • 1
Krauss
  • 998
  • 10
  • 17
  • 2
    This answer is incredibly helpful. You won't find any concrete `RETURNING id INTO [new_variable]` examples in the official Postrgres docs. – Chris Kobrzak Sep 28 '20 at 21:43
35

The other answers don't show how one might use the value(s) returned by RETURNING. Here's an example where the returned value is inserted into another table.

WITH inserted_id AS (
  INSERT INTO tbl1 (col1)
  VALUES ('foo') RETURNING id
)

INSERT INTO tbl2 (other_id) 
VALUES ((select id from inserted_id));
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
  • 1
    Top! Most straight-to-the-point answer – Anytoe Oct 07 '21 at 20:55
  • 1
    This gives kind of unexpected behavior if the tables are the same, in a test I did I got two records like {id: 2, lastid: null} and {id: 1, lastid: 2} – SteveC Jan 22 '22 at 15:00
  • 2
    I don't think Postgres provides any guarantees about the evaluation order in this situation (that's my reading of [this section](https://www.postgresql.org/docs/13/queries-with.html#QUERIES-WITH-MODIFYING) from the docs. – snakecharmerb Jan 22 '22 at 16:15
  • Best answer - it runs immediately, and works exactly as intended. Thanks – Kent Bolton Jul 05 '23 at 08:49
34

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 
wgzhao
  • 560
  • 5
  • 5
15
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

Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
jishi
  • 24,126
  • 6
  • 49
  • 75
15

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'));
Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
RINSON KE
  • 282
  • 1
  • 5
14

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.

Mohammad Fallah
  • 976
  • 11
  • 14
12

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.

emreoktem
  • 2,409
  • 20
  • 36
9

The better way is to use Insert with returning. Though there are already same answers, I just want to add, if you want to save this to a variable then you can do this

insert into my_table(name) returning id into _my_id;
Sunil Garg
  • 14,608
  • 25
  • 132
  • 189
3

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)
Sandip Debnath
  • 995
  • 8
  • 7
2

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.

Oozman
  • 41
  • 4
0

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

Cristian
  • 548
  • 6
  • 8
-1

Based on @ooZman 's answer above, this seems to work for PostgreSQL v12 when you need to INSERT with the next value of a "sequence" (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->query("select (setval('"your_id_seq"', (select nextval('"your_id_seq"')) - 1, true)) +1");
ruleboy21
  • 5,510
  • 4
  • 17
  • 34
K8sN0v1c3
  • 109
  • 1
  • 10
  • There is no guarantee the step size is 1, and if there are any concurrent uses of the sequence this blows up horribly. – Mike Miller Apr 28 '23 at 19:32