How to Reset the Primary Key Sequence ID in PostgreSQL

brown wooden bench in field

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.

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.

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

Source: How to reset postgres’ primary key sequence when it falls out of sync?


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.