Sequence does not reset after truncating the table

问题:

I use SELECT lastval() to get wrong serial id after truncated the table.

when I truncate the table, I use SELECT lastval(), I got the wrong ID/

回答1:

Try

TRUNCATE TABLE table_name 
RESTART IDENTITY;

It will

Automatically restart sequences owned by columns of the truncated table(s).

Details here: TRUNCATE

回答2:

Following is the standard way to reset sequence:

truncate table table_name restart identity;

but in some version & platform, it's syntax error,

in that case, you can truncate without reset sequence, and alter the sequence with another sql, try this:

truncate table table_name;
alter sequence seq_name start 1;

回答3:

Check the next

ALTER SEQUENCE sequence_name RESTART WITH 1;

回答4:

The best way to reset a sequence to start back with number 1 is to execute the following after you have successfully truncate it:

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1

So, for example for the users table it would be:

ALTER SEQUENCE users_id_seq RESTART WITH 1

回答5:

If you want to reset the sequence then:

setval('sequence_name', 0)

To list the existent sequence names issue a \ds at the psql prompt.

回答6:

Try this:

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;