Take the 2-minute tour ×
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.

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

2 Answers 2

up vote 18 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));
CREATE TABLE
postgres=> insert into names (name) values ('Arthur Dent');
INSERT 0 1
postgres=> select currval('names_id_seq');
 currval
---------
       1
(1 row)
postgres=>
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
4  
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 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 at 14:01

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

Your Answer

 
discard

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.