If you use PostgreSQL and have tables with auto-increment primary keys, there may be times when you want to reset the numbering of the sequence of the primary key integer. How do you reset the next integer ID in the primary key? Read on.
How to reset the sequence of a SERIES in PostgreSQL
Scenario
For our example, let us look at a table accounts with a column id which is an integer primary key and has auto-increment enabled with SERIAL.
The schema of your table may be like this:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name character varying(80 NOT NULL,
username character varying(20) UNIQUE NOT NULL,
password character varying(80) NOT NULL,
created_at timestamp DEFAULT now(),
updated_at timestamp DEFAULT now()
);
The sequence gets saved as accounts_id_seq.
Now, you have a Python application that uses this table. There are a lot of row insertions, updations and deletions.
After a while, you find that there have been far too many rows deleted. The next available ID in the accounts sequence is 1000. But the last ID in accounts is 200.
How do you make the next inserted row to have ID 201?
Solution: Reset the sequence
In PostgreSQL, the command to reset the sequence is ALTER SEQUENCE seq_name RESTART WITH new_id_value;
In our case, we will do this:
ALTER SEQUENCE accounts_id_seq RESTART WITH 201;
That's all. Next time there is an insertion of a record, that record will automatically take the ID 201.
List sequences
To list the available sequences in PostgreSQL:
\ds
List next ID in sequence
To see the next available ID in the sequence:
$ psql acsl -c "select * from accounts_id_seq"
last_value | log_cnt | is_called
------------+---------+-----------
201 | 30 | t
(1 row)
Conclusion
Hopefully, this worked for you. If you are unable to reset a Postgres sequence, let me know in the comments or by email. Thanks for reading.
Related Posts
If you have any questions, please contact me at arulbOsutkNiqlzziyties@gNqmaizl.bkcom. You can also post questions in our Facebook group. Thank you.