CODEWITHSUNDEEP
postgresql
Oto Shavadze
Oto Shavadze

Reputation: 42753

Reset sequence value as 1

I need that after TRUNCATE table, reset his sequence, for this I do :

SELECT setval('mytable_id_seq', 1)

After, when insert rows in table, sequence is started from 2 not 1

How to resert sequence value such, that new first value will be 1?

 SELECT setval('mytable_id_seq', 0) // gives error that value is out of range

Upvotes: 8

Views: 9942

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

Either use the third argument for setval():

setval(yourseq, 1, false)

http://www.postgresql.org/docs/current/static/functions-sequence.html

Or alter the sequence:

alter sequence yourseq restart

http://www.postgresql.org/docs/current/static/sql-altersequence.html

Upvotes: 7

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

First set the minimum value of the sequence

alter sequence mytable_id_seq minvalue 0 start with 1;

Now either reset it:

SELECT setval('mytable_id_seq', 0)

Or reset it while truncating:

truncate mytable restart identity;

Upvotes: 21

Related Questions