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.
TRUNCATE command will delete ALL data!!!
TRUNCATE <table name> RESTART IDENTITY;
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>;
ALTER SEQUENCE product_id_seq RESTART WITH 1000;