If you’ve ever needed to “reset” the auto-incrementing row ID in a Postgres database here is a simple command to start fresh (this should work for any SQL based DB really). Typically during the development life cycle I want to generate new dummy data for testing purposes. Sometimes that data is in a table with an auto-incrementing ID and I use the TRUNCATE command to remove all data and reset the auto-increment value to 0 for a given table.
WARNING The TRUNCATE command will delete ALL data!!!
1 | TRUNCATE <table name> RESTART IDENTITY; |
If the TRUNCATE command is a little too intense, i.e. you want to keep some data, you can also consider the ALTER SEQUENCE command to set the auto-incrementing ID column to a specified value. The sequence for a column is not just the column name, but is formatted like <table name>_<column name>_seq. For example:
1 | ALTER SEQUENCE <table name>_<column name>_seq RESTART WITH <reset id>; |
Example Use:
Table: Product
Column: _id_
1 | ALTER SEQUENCE product_id_seq RESTART WITH 1000; |