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?

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.

StackExchange Database Administrators Q#3281, answer score: 78