Reset Auto-Increment IDs in Postgres


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!!!


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:

ALTER SEQUENCE <table name>_<column name>_seq RESTART WITH <reset id>;

Example Use:

Table: Product
Column: _id_

ALTER SEQUENCE product_id_seq RESTART WITH 1000;