67

I have a table:

CREATE TABLE names (id serial, name varchar(20))

I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don't understand how to use it.

I have tried with:

SELECT CURRVAL() AS id FROM names_id_seq
SELECT CURRVAL('names_id_seq')
SELECT CURRVAL('names_id_seq'::regclass)

but none of them work. How can I use currval() to get the last inserted id?

  • 2
    Readers of this problem/solution ought to be aware that the use of currval() is generally discouraged as the RETURNING clause provides the identifier without the overhead of the additional query, and without the possibility of returning the WRONG VALUE (which currval will do in some use cases.) – chander Aug 15 '17 at 12:52
  • 1
    @chander: do you have any reference for that claim? The use of currval() is most definitely not discouraged. – a_horse_with_no_name Sep 7 '18 at 18:55
  • Perhaps it's a matter of opinion as to whether the use of currval is discouraged, but in certain cases users should be aware that it may deliver a value that isn't what you expect (thus making RETURNING the better choice where supported.) Suppose you have table A that uses sequence a_seq, and table B which also uses a_seq (calling nextval('a_seq') for the PK column.) Suppose you also have a trigger (a_trg) that inserts into table B ON INSERT to table A. In that case the currval() function (after insert on table A) will return the number generated for the insert on table B, not table A. – chander Sep 7 '18 at 20:53
53

If you create a column as serial PostgreSQL automatically creates a sequence for that.

The name of the sequence is autogenerated and is always tablename_columnname_seq, in your case the sequence will be names names_id_seq.

After inserting into the table, you can call currval() with that sequence name:

postgres=> CREATE TABLE names in schema_name (id serial, name varchar(20));
CREATE TABLE
postgres=> insert into names (name) values ('Arthur Dent');
INSERT 0 1
postgres=> select currval('names_id_seq');
 currval
---------
       1
(1 row)
postgres=>

Instead of hardcoding the sequence name, you can also use pg_get_serial_sequence() instead:

select currval(pg_get_serial_sequence('names', 'id'));

That way you don't need to rely on the naming strategy Postgres uses.

Or if you don't want to use the sequence name at all, use lastval()

  • I guess it isn't good to use currval() in a multi-user setup. E.g on a webserver. – Jonas Jun 13 '11 at 11:29
  • 9
    No you are mistaken. currval() is "local" to your current connection. So there is no problem using it in a multi-user environment. That's the whole purpose of a sequence. – a_horse_with_no_name Jun 13 '11 at 11:31
  • 1
    This could break in the (quite rare) case in which your insert triggers more inserts in the same table, no? – leonbloy Mar 26 '14 at 13:58
  • 1
    @a_horse_with_no_name : I was thinking not on multiple inserts (that's easy to spot) but on (perhaps unknown) triggers defined on the table. Try for example this on the above: gist.github.com/anonymous/9784814 – leonbloy Mar 26 '14 at 14:36
  • 1
    It's not ALWAYS the table and column name. If there is already a sequence with that name then it will generate a new sequence by concatenating or incrementing a number on the end, and it may need to shorten the name if it exceeds the limit (which appears to be 62 characters). – PhilHibbs Jul 21 '16 at 15:12
49

This is straight from Stack Overflow

As it was pointed out by @a_horse_with_no_name and @Jack Douglas, currval works only with the current session. So if you are ok with the fact that the result might be affected by an uncommitted transaction of another session, and you still want something that will work across sessions, you can use this:

SELECT last_value FROM your_sequence_name;

Use the link to SO for more information.

From Postgres documentation though, it is clearly stated that

It is an error to call lastval if nextval has not yet been called in the current session.

So I guess strictly speaking in order to properly use currval or last_value for a sequence across sessions, you would need to do something like that?

SELECT setval('serial_id_seq',nextval('serial_id_seq')-1);

Assuming, of course, that you will not have an insert or any other way of using the serial field in the current session.

  • 3
    I cannot think of a situation when this would be useful. – ypercubeᵀᴹ Oct 2 '14 at 11:14
  • I was just wondering if this is a way to get currval, if nextval hasn't been called in the current session. Any suggestions then? – Slak Oct 2 '14 at 14:30
  • I had to do this when I was hard-coding primary keys for generated fixture data where I wanted the PK values that would normally be generated incrementally to be determined ahead of time to make client testing easier. In order to support inserts when doing that on a column that is normally governed by the default value from a nextval() you then have to manually set the sequence to match the number of fixture records you inserted with the hardcoded IDs. Also, the way to solve for the problem of currval() / lastval() not being available pre-nextval is to just SELECT on the sequence directly. – Peter M. Elias Sep 19 '16 at 17:07
  • @ypercubeᵀᴹ On the contrary, I can think of no good reason to use the "correct" answer that was chosen. This answer doesn't require inserting a record into the table. This answers the question as well. Again, I can think of no good reason NOT to use this answer over the one chosen. – Henley Chiu Feb 13 '17 at 21:09
  • 1
    This answer does not involve modifying the table at all. The checked one does. Ideally, you just want to know the last ID without making ANY changes at all. What if this is a production DB? You can't just insert a random row without possible percussions. Thus this answer is safer as well as correct. – Henley Chiu Feb 14 '17 at 21:26
14

You need to call nextval for this sequence in this session before currval:

create sequence serial;
select nextval('serial');
 nextval
---------
       1
(1 row)

select currval('serial');
 currval
---------
       1
(1 row)

so you cannot find the 'last inserted id' from the sequence unless the insert is done in the same session (a transaction might roll back but the sequence will not)

as pointed out in a_horse's answer, create table with a column of type serial will automatically create a sequence and use it to generate the default value for the column, so an insert normally accesses nextval implicitly:

create table my_table(id serial);
NOTICE:  CREATE TABLE will create implicit sequence "my_table_id_seq" for 
         serial column "my_table.id"

\d my_table
                          Table "stack.my_table"
 Column |  Type   |                       Modifiers
--------+---------+-------------------------------------------------------
 id     | integer | not null default nextval('my_table_id_seq'::regclass)

insert into my_table default values;
select currval('my_table_id_seq');
 currval
---------
       1
(1 row)
3

So there are some issues with these various methods:

Currval only gets the last value generated in the current session - which is great if you don't have anything else generating values, but in cases where you might call a trigger and/or have the sequence advanced more than once in the current transaction it's not going to return the correct value. That's not an issue for 99% of the people out there - but it's something that one should take into consideration.

The best way to get the unique identifier assigned after an insert operation is using the RETURNING clause. The example below assumes that the column tied to the sequence is called "id":

insert into table A (cola,colb,colc) values ('val1','val2','val3') returning id;

Note that the usefulness of the RETURNING clause goes well beyond just getting the sequence, since it will also:

  • Return values that were used for the "final insert" (after, for example a BEFORE trigger might have altered the data being inserted.)
  • Return the values that were being deleted:

    delete from table A where id > 100 returning *

  • Return the modified rows after an UPDATE:

    update table A set X='y' where blah='blech' returning *

  • Use the result of a delete for an update:

    WITH A as (delete * from table A as returning id) update B set deleted=true where id in (select id from A);

  • Of course, the OP explicitly said they didn't want to use a RETURNING clause - but, there's nothing wrong with making the benefits of using it clearer to others. – RDFozz Aug 4 '17 at 14:41
  • I was really just trying to point out the pitfalls of the various other methods (in that unless one was careful it could return a value other than the value expected) - and clarify the best practice. – chander Aug 8 '17 at 20:27
1

I had to execute a query despite using SQLALchemy because I wasn't successful of using currval.

nextId = db.session.execute("select last_value from <table>_seq").fetchone()[0] + 1

This was a python flask + postgresql project.

1

If you want to get the value of the sequences without having called nextval(), and without having to modify the sequence, I threw together a PL/pgSQL function to handle it: Find the value of all database sequences

1

You need to GRANT usage on schema, like this:

GRANT USAGE ON SCHEMA schema_name to user;

and

GRANT ALL PRIVILEGES ON schema_name.sequence_name TO user;
  • 1
    Welcome to dba.se! Cheers on your first post! It doesn't look like the Original Post is specifically regarding permissions. Perhaps consider expanding your answer to include some specifics around permissions failures when calling the currval() function to make it a bit more relevant to this thread? – Peter Vandivier Dec 12 '18 at 15:36
0

In PostgreSQL 11.2 you can treat the sequence like a table it seems:

Example if you have a sequence named: 'names_id_seq'

select * from names_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
          4 |      32 | t
(1 row)

That should give you the last inserted id (4 in this case) which means that the current value (or the value that should be used for the id next) should be 5.

-2

Different versions of PostgreSQL may have different functions to get the current or next sequence id.

First, you have to know the version of your Postgres. Using select version(); to get the version.

In PostgreSQL 8.2.15, you get the current sequence id by using select last_value from schemaName.sequence_name.

If the above statement doesn't work, you can use select currval('schemaName.sequence_name');

  • 2
    Any proof for different versions doing it differently? – dezso Oct 28 '15 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.