Dealing with 'duplicate key violates unique constraint' on the primary key

Posted by Luke Francl
on Thursday, March 05

I recently had to work through a problem where inserts were failing due to duplicate primary keys.

Here’s the error (edited for clarity):

PGError: ERROR: duplicate key violates unique constraint "contracts_pkey": INSERT INTO "contracts" ('column_1', 'column_2', 'column_3') VALUES('abc', '123', 'xyz') RETURNING "id"

What is going on here? I’m not even providing the primary key id—that comes from the sequence.

Hey wait a second…

What was happening is that we had a data import that didn’t use the sequence. So the integers returned by the sequence have already been used, causing a duplicate primary key.

To solve the problem, I had to reset the sequence, like this:

select setval('contracts_id_seq', (select max(id) + 1 from contracts));