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.
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()
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)
So there are some issues with these various methods:
Currval only gets the last value generated in the current session - which is great if you don't have anything else generating values, but in cases where you might call a trigger and/or have the sequence advanced more than once in the current transaction it's not going to return the correct value. That's not an issue for 99% of the people out there - but it's something that one should take into consideration.
The best way to get the unique identifier assigned after an insert operation is using the RETURNING clause. The example below assumes that the column tied to the sequence is called "id":
insert into table A (cola,colb,colc) values ('val1','val2','val3') returning id;
Note that the usefulness of the RETURNING clause goes well beyond just getting the sequence, since it will also:
Return the values that were being deleted:
delete from table A where id > 100 returning *
Return the modified rows after an UPDATE:
update table A set X='y' where blah='blech' returning *
Use the result of a delete for an update:
WITH A as (delete * from table A as returning id) update B set deleted=true where id in (select id from A);
In PostgreSQL 11.2 you can treat the sequence like a table it seems:
Example if you have a sequence named: 'names_id_seq'
select * from names_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
4 | 32 | t
(1 row)
That should give you the last inserted id (4 in this case) which means that the current value (or the value that should be used for the id next) should be 5.
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.
If you want to get the value of the sequences without having called nextval(), and without having to modify the sequence, I threw together a PL/pgSQL function to handle it: Find the value of all database sequences
You need to GRANT usage on schema, like this:
GRANT USAGE ON SCHEMA schema_name to user;
and
GRANT ALL PRIVILEGES ON schema_name.sequence_name TO user;
select *, 'select '''||secuencia||''' as secuencia, nextval('''||secuencia||''') as currentval, (select max('||campo||') from '||tabla||') as maxtabla UNION ALL ' as sentencia from (
select
relname as secuencia
, substr(relname,0, strpos(relname, SPLIT_PART(relname, '_', nseparadores))-1 ) as tabla -- tabla sobre el que hace referencia la secuencia
--, strpos(relname, SPLIT_PART(relname, '_', nseparadores)) as posicion_campo --posición donde comienza el nombre del campo
,SPLIT_PART(relname, '_', nseparadores) as campo -- campo sobre el que hace referencia la secuencia
from (
select nspname, relname, length(relname) - length(replace(relname, '_', '')) as nseparadores from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
) sentencia01
) sentencia02
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');
