April 24, 2020 · 1 min read

How to Reset the Primary Key Sequence ID in PostgreSQL

Reset PostgreSQL primary key sequences that fall out of sync using setval and pg_get_serial_sequence.

I run into an issue today with PostgreSQL. I had manually entered a row into a table and it caused the primary key to fall out of sequence.

bash

SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint...

To reset the primary key sequence ID on the PostgreSQL table I used the setval and pg_get_serial_sequence functions.

sql

SELECT setval(pg_get_serial_sequence('table_name_here', 'id'), COALESCE((SELECT MAX(id) + 1 FROM table_name_here), 1), false);