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?

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


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()
