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;
From PostgreSQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and PostgreSQL
Previous
From PostgreSQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and PostgreSQL