postgresql - How to reset a Postgres IDENTITY column to its original first value - Stack Overflow

admin管理员组

文章数量:1123035

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?

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?

• postgresql
• sequence
• identity

Share Improve this question Follow asked 2 hours ago user14381362user14381362 35811 gold badge33 silver badges1111 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value. The value that will be reported by currval is also set to the specified value. In the three-parameter form, is_called can be set to either true or false. true has the same effect as the two-parameter form.
If it is set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. Furthermore, the value reported by currval 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

1. Add another owned sequence
2. Drop the identity

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

3. Re-add identity to the column

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.

本文标签： postgresqlHow to reset a Postgres IDENTITY column to its original first valueStack Overflow