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?

  • 3
    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, 2017 at 12:52
  • 1
    @chander: do you have any reference for that claim? The use of currval() is most definitely not discouraged.
    – user1822
    Sep 7, 2018 at 18:55
  • 3
    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, 2018 at 20:53
  • I'm with @chander for safety. In a busy db, having a stored procedure potentially / occasionally get an identity value back from an insert that is not correct could be disastrous; or at least be the cause of an intermittent problem that will be hard to find. Perhaps your 'worst case' might be that you create some kind of log message with the wrong id value... but what if your procedure starts creating child records for the wrong parent? That could impact real people; e.g. (as an extreme example) attach repayment records intended for one loan to a totally different loan! Or medical treatments.
    – Nij
    Jul 6, 2021 at 5:55

10 Answers 10


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));
postgres=> insert into names (name) values ('Arthur Dent');
postgres=> select currval('names_id_seq');
(1 row)

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, 2011 at 11:29
  • 13
    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.
    – user1822
    Jun 13, 2011 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, 2014 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, 2014 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, 2016 at 15:12

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. Oct 2, 2014 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, 2014 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. Sep 19, 2016 at 17:07
  • 1
    @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. Feb 13, 2017 at 21:09
  • 2
    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. Feb 14, 2017 at 21:26

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

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

select currval('serial');
(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');
(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.
    – RDFozz
    Aug 4, 2017 at 14:41
  • 1
    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, 2017 at 20:27

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.


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;


GRANT ALL PRIVILEGES ON schema_name.sequence_name TO user;
  • 2
    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? Dec 12, 2018 at 15:36
select  *, 'select '''||secuencia||''' as secuencia, nextval('''||secuencia||''') as currentval, (select max('||campo||') from '||tabla||') as maxtabla UNION ALL ' as sentencia from (
relname as secuencia
, substr(relname,0, strpos(relname, SPLIT_PART(relname, '_', nseparadores))-1 ) as tabla -- tabla sobre el que hace referencia la secuencia
--, strpos(relname, SPLIT_PART(relname, '_', nseparadores)) as posicion_campo --posición donde comienza el nombre del campo
,SPLIT_PART(relname, '_', nseparadores) as campo -- campo sobre el que hace referencia la secuencia
from (
select nspname, relname, length(relname) - length(replace(relname, '_', '')) as nseparadores  from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
) sentencia01
) sentencia02
  • 2
    This code fragment might indeed answer the question, however, it will be much better if you add an explanation how it works and why it is different from all other answers.
    – mustaccio
    Jun 19, 2020 at 18:54

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? Oct 28, 2015 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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