How do I use currval() in PostgreSQL to get the last inserted id?

  • 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?

    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: do you have any reference for that claim? The use of `currval()` is most definitely not discouraged.

    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.

  • 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.

    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.

    Ah, that's great! That also explains why I had problems, since I inserted with one connection and read currval() with another. Thanks.

    This could break in the (quite rare) case in which your insert triggers more inserts in the same table, no?

    @a_horse_with_no_name Some English parsing problem here: "insert (noun) triggers (verb)". I mean: suppose you have some trigger that, on insertion on table `names`, it inserts (on some condition) an extra row to the same table...

    @leonbloy: the `insert` as shown, will not create more than one row. But you are right: if you run an insert that inserts more than one row (e.g. `insert into .. values (...), (...)`) then you can not use `currval()` as shown.

    @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: https://gist.github.com/anonymous/9784814

    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).

  • 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.

    I cannot think of a situation when this would be useful.

    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?

    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.

    @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.

    @HenleyChiu I'm not sure I follow you. The question asked says *"I want the "last inserted id" from that table, ..."* and you say *"This answer doesn't require inserting a record into the table."* If your requirements are different than the question asked, ok.

    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.

    I can think of a situation when this would be useful. thanks!

    That "-1" might not work when sequence has increment greater than 1. And `SELECT last_value FROM your_sequence_name;` seems to work even as first query in session, that is different from `lastval(your_sequence_name)`

  • 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)
    
  • 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.

    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.

  • 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.

  • 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

  • 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;
    

    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?

  • 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.

  • 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');

    Any proof for different versions doing it differently?

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM