Explain Codes LogoExplain Codes Logo

Reset auto increment counter in postgres

sql
auto-increment
sequences
best-practices
Alex KataevbyAlex Kataev·Aug 17, 2024
TLDR

To reset the PostgreSQL auto-increment counter, use:

ALTER SEQUENCE table_name_column_seq RESTART WITH 1;

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:

SELECT SETVAL('sequence_name', (SELECT MAX(column_name) FROM table_name));

Unmask the sequence name

PostgreSQL uses the naming convention tablename_columnname_seq for sequences, but you can confirm with:

SELECT pg_get_serial_sequence('table_name', 'column_name');

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:

SELECT SETVAL('sequence_name', (SELECT MAX(column_name) FROM table_name), false); -- Hey! You can't collect $200 until you pass Go. So, stay on the current 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.