Duplicate key violates unique constraint

Duplicate key violates unique constraint

A few days ago I got this weird error with one of my PostgreSQL databases:

duplicate key violates unique constraint.

Turns out the DB (or Rails) was trying to write the ids at least 2000 numbers lower than the highest number there, so Rails couldn’t save to it.

While the error in itself seemed messy, the fix was a really easy one.

First verify if that is indeed your problem:

SELECT MAX(id) FROM table_name;
SELECT nextval('table_name_id_seq');

If the second number is lower than the first, then you’re in luck because you just have to run this little piece of code and you’ll be on your way to Merry Codeland (Of course, please do your due diligences and backup your databases!):

SELECT setval('table_name_id_seq', (SELECT MAX(id) FROM table_name)+1);

Reload pgsql and rails, and see if it works now. You’re welcome :)

Show Comments