Causes and Solutions for the PostgreSQL Error: "ERROR: duplicate key violates unique constraint 'table_name_pkey'"
This page is an English translation of the original Japanese article. The PostgreSQL error messages shown here are translated from their Japanese versions and may differ from the official English messages output by PostgreSQL.
This article explains why the PostgreSQL error "duplicate key violates unique constraint" occurs and how to resolve it. The issue is commonly caused by duplicate primary key values or a sequence that has fallen behind the actual maximum value in the table. Using practical examples, this page walks through how to identify the cause and how to correct the sequence to prevent the error from occurring again.
In a PostgreSQL database, when you attempt to perform an INSERT operation and the primary key value is not unique, an error like the one shown above will occur.
Example: Executing the following SQL statements:
INSERT INTO nobuneko_table (nobu_id, neko_id) VALUES (1, 105);
INSERT INTO nobuneko_table (nobu_id, neko_id) VALUES (1, 200);
*nobuneko_table: table name, nobu_id: primary key*
When running the SQL above, the following error will be raised:
ERROR: duplicate key violates unique constraint "nobuneko_table_pkey"
You can avoid this error by reviewing the SQL so that the primary key value is unique (i.e., does not duplicate an existing value).
If the primary key is automatically generated by the system using a sequence, you must check whether the automatically assigned value duplicates an existing value in the database and correct it if necessary. If the sequence value is smaller than the maximum primary key value already stored in the table, the sequence will eventually reach an existing primary key value as it increments, causing the error above. To set the sequence value to the current maximum primary key value, you can run SQL like the following:
select setval('sequence_name', (select max(primary_key) from table_name));
select setval('nobuneko_table_nobu_id_seq', (select max(nobu_id) from nobuneko_table));