First, I'm aware of this answer and it does not do what I want
I need to reset IDENTITY
so that next insert generates a 1, but it inserts a 2 instead:
create table test(
id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
NAME text not null
);
insert into test(name) values('a');
SELECT id FROM test;
--1
DELETE FROM test;
SELECT setval(pg_get_serial_sequence('test', 'id'), 1)
insert into test(name) values('a');
SELECT id FROM test;
--2
I can delete, reset IDENTITY, and re-INSERT again multiple times, and always get id=2
.
Is this a bug in Postgres? Is there a way to make my subsequent inserts have id=1
?
Set is_called
, the third argument of setval()
, to false
:
demo at db<>fiddle
select setval(pg_get_serial_sequence('test', 'id'), 1, false);
insert into test(name)values('a')returning*;
id | name |
---|---|
1 | a |
setval ( regclass, bigint [, boolean ] ) → bigint
Sets the sequence object's current value, and optionally its
is_called
flag. The two-parameter form sets the sequence'slast_value
field to the specified value and sets itsis_called
field totrue
, meaning that the nextnextval
will advance the sequence before returning a value. The value that will be reported bycurrval
is also set to the specified value. In the three-parameter form,is_called
can be set to eithertrue
orfalse
.true
has the same effect as the two-parameter form.
If it is set tofalse
, the nextnextval
will return exactly the specified value, and sequence advancement commences with the followingnextval
. Furthermore, the value reported bycurrval
is not changed in this case. For example,SELECT setval('myseq', 42); Next nextval will return 43 SELECT setval('myseq', 42, true); Same as above SELECT setval('myseq', 42, false); Next nextval will return 42
The result returned by
setval
is just the value of its second argument.
As pointed out by @Adrian Klaver below, you can save the sequence name lookup if you use alter table
instead:
DELETE FROM test;
ALTER TABLE test ALTER COLUMN id RESTART;
If you're wiping and re-populating the table, you can further simplify things by merging the deletion with the restart, switching to truncate
:
TRUNCATE test RESTART IDENTITY;
There's a significant advantage of this over the lookup: pg_get_serial_sequence()
can go out of sync with what's really guarding the identity. The function just returns the first sequence owned by the column which means if you
The pg_get_serial_sequence()
function will not only continue to report a sequence name, it will now return the next owned sequence, regardless of whether it's the new default
for the column, which it doesn't have to be. Then if you
The function will continue to return the non-identity sequence, and thus this nextval()
will attempt to manipulate the wrong thing. It's a rare scenario but still, alter
and truncate
are guaranteed to always use the actual identity sequence so they're just safer on top of being simpler and clearer.