PostgreSQL: Reset Sequence Command

If you have a serial ID column (ie auto incrementing ID), they'll start at 1 by default, but sometimes you may want them to start at a different number. These numbers are known as "sequences" and have their own designated table.

If you have a users.id column, you'll have a users_id_seq table. Some helpful columns in there are start_value, which will usually be 1, and last_value, which could be a fast way to see how many rows are in your table if you haven't altered your sequence or deleted any rows.

select * from users_id_seq;

 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 users_id_seq  |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
(1 row)

To alter the sequence so that IDs start a different number, you can't just do an update, you have to use the alter sequence command.

alter sequence users_id_seq restart with 1000;

When you're truncating a table, you can truncate and restart IDs from 1 in one command:

truncate bad_users restart identity;