Reset auto increment counter in postgres
To reset the PostgreSQL auto-increment counter, use:
Replace table_name
and column
with your table and auto-increment column names. Change 1
to the value you want the next increment to use.
And to match your sequence with the table's highest existing ID, run:
Unmask the sequence name
PostgreSQL uses the naming convention tablename_columnname_seq
for sequences, but you can confirm with:
This can help you avoid a manual entry error.
Busting the common misconceptions
TRUNCATE TABLE table_name RESTART IDENTITY;
is a Data Kraken that will devour all your data in addition to resetting the sequence.- If the sequence is partying away from the table's max ID, you can bring it home with the
SETVAL
command mentioned above. - Errors often mean the sequence name isn't correct—so keep calm and double-check.
PostgreSQL sequences vs Auto_increment
In PostgreSQL, it's the sequences not tables we tune up or down for auto-increment counters—opposite to some other SQL databases that use AUTO_INCREMENT
attribute on the table.
Accurately setting the start Round
To start the game from the appropriate Round after some have been penalized, run the SETVAL
with a subquery to find the current maximum Round:
Good practices: Keep an eye on sequences
Periodically list all sequences and their last values using \ds
command in psql. This way, you can knit the wool before the sheep are shorn—i.e., adjust sequences as needed.
Was this article helpful?