PostgreSQL allows you to define primary keys for your database tables, which ensure that your table rows are unique. By default, these primary keys have a starting value of 1 and auto increment by 1 for each row. Sometimes you may need to reset primary sequence in PostgreSQL. In this article, we will learn how to reset primary key sequence in PostgreSQL. This may also be required if you find that your primary key is not in sync with your table rows. For example, there may have been deletions in your table, and when you try inserting a row with a specific key value, it may give duplicate key error.
How to Reset Primary Key Sequence in PostgreSQL
Here are the steps to reset primary key sequence in PostgreSQL. Login to PostgreSQL and run the following query to get maximum primary key value.
SELECT MAX(id) FROM your_table;
Then run the following query to get your next primary key value. It should be higher than the value returned by above query.
SELECT nextval('your_table_id_seq');
If the value returned by above query is not higher than the value returned by the first query, run the following queries to reset it.
BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;
In the above query, we use setval() function to set the primary key value of the table.
In this article, we have learnt how to reset primary sequence in PostgreSQL.
Also read:
How to Generate Random String Characters in JavaScript
How to Fix PostgreSQL Error Fatal Role Does Not Exist
How to Install Python Package Using Script
How to Iterate Over List in Chunks
How to Create Python Dictionary from String