CODEWITHSUNDEEP
postgresqlsequence
user1369887
user1369887

Reputation: 551

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/

Upvotes: 45

Views: 49921

Answers (7)

Hafiz Hashim
Hafiz Hashim

Reputation: 394

You can use the below sql statement to reset the identity in postgresql:

TRUNCATE TABLE table_name RESTART IDENTITY;

But note, it's not possible to reseed the identity in Redshift. The way around to reset in Redshift is to drop and then create the table again.

Upvotes: 1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28511

Use the TRUNCATE SQL command.

For a single table the syntax is the following:

TRUNCATE TABLE table_name RESTART IDENTITY;

For multiple tables:

TRUNCATE TABLE table_foo, table_bar RESTART IDENTITY;

What it does:

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

Details here: TRUNCATE @ postgresql.org

Upvotes: 113

5a01d01P
5a01d01P

Reputation: 683

Try this:

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

Upvotes: 5

Eric
Eric

Reputation: 24866

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;

Upvotes: 26

jahmed31
jahmed31

Reputation: 3766

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

Upvotes: 8

Daniel Gast&#243;n
Daniel Gast&#243;n

Reputation: 96

Check the next

ALTER SEQUENCE sequence_name RESTART WITH 1;

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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.

Upvotes: 2

Related Questions