Duplicate Key Error: "ERROR: duplicate key violates unique constraint 'table_pkey'" in PostgreSQL -- Causes and Solutions
This page is an English translation of the original Japanese article. The PostgreSQL error messages included here are translated from their Japanese versions, and may not exactly match the official English error messages produced by PostgreSQL.
When inserting a record into a PostgreSQL database table, if you encounter an error message such as "ERROR: duplicate key violates unique constraint 'table_name_pkey' DETAIL: Key (table_column_name)=(10) already exists," the cause is that the primary key value of the record you are trying to insert already exists in another record in the table. This error occurs because the table has a constraint requiring the value of a specific column to be unique, and the insert operation violates that constraint. To resolve this error, simply change the value to a unique one (a value that does not duplicate any existing record) before inserting.
If the primary key values are automatically assigned using a sequence, the cause of this error may be related to the sequence configuration. If you suspect that the sequence is the source of the problem, check whether the sequence contains incorrect values and reconfigure it if necessary.
Prerequisites
Assume that the table in question contains 100 records, and the value of "table_column_name" in each record is a number between 1 and 100, with no duplicates.
Check the table's columns, types, and modifiers
After connecting to the database with psql, run "\d table_name" to check the table information.
database_name=> \d table_name
Table "public.table_name"
Column | Type | Modifiers
-----------------------+--------------------------------+--------------------------------------------------------------
table_column_name | integer | not null default nextval('table_name_column_name_seq'::regclass)
table_column_name2 | timestamp without time zone |
table_column_name3 | text |
From this output, we can see that the problematic column "table_column_name" has the type "integer" and the modifier "not null default nextval", which indicates that a sequence is being used. The sequence name is "table_name_column_name_seq".
Check the current sequence value
You can check the current sequence value by running "select last_value from sequence_name;".
database_name=> select last_value from table_name_column_name_seq;
last_value
------------
11
(1 row)
Based on the "Prerequisites" above, it is incorrect for the current sequence value to be 11. It should normally be 100.
Reset the sequence value
Resetting the sequence value resolves the issue, so we will reconfigure it.
You can reset the sequence using "select setval('sequence_name', number, true);".
If you specify true, the given number becomes the current sequence value, and the next nextval will return "number + 1". If you specify false, the given number becomes the current sequence value, and the next nextval will return "number".
Below are two methods: resetting the sequence to a specific number and resetting it using the maximum value of the table column that uses the sequence.
Resetting the sequence to a specific number
Running "select setval('table_name_column_name_seq', 100, true);" sets the current sequence value to 100, and nextval will return 101.
database_name=> select setval('table_name_column_name_seq', 100, true);
setval
--------
100
(1 row)
Resetting the sequence using the maximum value of the table column
Running "select setval('table_name_column_name_seq', (select max(table_column_name) from table_name), true);" sets the current sequence value to the maximum value of "table_column_name" (=100), and nextval will return 101.
database_name=> select setval('table_name_column_name_seq', ( select max(table_column_name) from table_name), true);
setval
--------
100
(1 row)