PostgreSQL error “duplicate key violates unique constraint”

When insert data into PostgreSQL and getting the error of “ERROR: duplicate key violates unique constraint”. That means the primary key has out of sync. 

Need to check the database

SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval(‘the_primary_key_sequence’);

if both working fine, then use this query

SELECT setval(‘the_primary_key_sequence’, (SELECT MAX(the_primary_key) FROM the_table)+1);

 

My case:

SELECT setval(‘users_id_seq’::regclass, (SELECT MAX(id) FROM users)+1);

Advertisements