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

3 Answers 3

up vote 20 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

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
1  
I cannot think of a situation when this would be useful. –  ypercube Oct 2 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 at 14:30

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.