Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 26 down vote accepted

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)
share|improve this answer
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
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
Ah, that's great! That also explains why I had problems, since I inserted with one connection and read currval() with another. Thanks. – Jonas Jun 13 '11 at 11:32
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
@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 Mar 26 '14 at 14:01

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 ""

\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)
share|improve this answer

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.

share|improve this answer
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 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.

share|improve this answer

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

share|improve this answer
Any proof for different versions doing it differently? – dezso Oct 28 '15 at 16:34
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – jkavalik Oct 28 '15 at 17:06
When I post the answer, for some reason, not all of the posts were posted. here is the full answer <p> Different version of postgresql may have different function to get the current or next sequence id. <p> first, you have to know the version of your postgres. using select version(); to get the version</p> <p> In postgresql 8.2.15, you get the current sequence id by using select last_value from schemaName.sequence_name.</p> <p> if the above statement doesn't work, you can use select currval('chemaName.sequence_name'); – Robinson Oct 28 '15 at 17:09
There's no problem pasting that text. No need for <p> or </p> either. I have edited your post for you. – Paul White Oct 28 '15 at 17:37
Thanks Paul. I believe that when I hit enter, it submits my post. – Robinson Oct 28 '15 at 17:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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