Resetting a Primary Key Sequence in PostgreSQL
In PostgreSQL, sequences are often used to automatically generate unique values for primary keys. However, if data is deleted or manually inserted into a table, the sequence can fall out of sync with the actual data, leading to duplicate key errors. To fix this, you can reset the primary key sequence to align with the maximum value currently in the column.
Syntax for Resetting a Primary Key Sequence:
The setval function allows you to reset a sequence by specifying the sequence name and the desired starting value. The following syntax aligns the sequence with the current maximum value in the primary key column:
SELECT setval('sequence_name', COALESCE(MAX(column_name), 1), true) FROM table_name;
Explanation:
Example:
Suppose you have a table called employees with a primary key column id and a sequence named employees_id_seq. To reset the sequence, you would run:
-- Reset the primary key sequence for the employees table
SELECT setval('employees_id_seq', -- Specify the sequence to reset
COALESCE(MAX(id), 1), -- Set the sequence to the max id, or 1 if empty
true) -- Next value will be max + 1
FROM employees; -- Target table for the operation
Explanation:
Steps to Identify and Reset the Sequence Name:
1. Identify Sequence Name: If you don’t know the sequence name, check by querying the pg_get_serial_sequence function:
SELECT pg_get_serial_sequence('table_name', 'column_name');
Example:
SELECT pg_get_serial_sequence('employees', 'id'); -- Finds the sequence for employees.id
2. Run the Reset Command: Using the identified sequence name, execute the setval command.
Important Notes:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4