On 1st of August Tom Lane committed patch from Itagaki Takahiro:
Improve unique-constraint-violation error messages to include the exact values being complained of. In passing, also remove the arbitrary length limitation in the similar error detail message for foreign key violations. Itagaki Takahiro
and later added an extension to it:
Department of second thoughts: let's show the exact key during unique index build failures, too. Refactor a bit more since that error message isn't spelled the same.
While it is not a super-complicated new feature, it's definitely useful to know why there is unique violation:
# CREATE TABLE test ( i int4 PRIMARY KEY ); NOTICE: CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "test_pkey" FOR TABLE "test" CREATE TABLE
# INSERT INTO test (i) VALUES (1); INSERT 0 1 # INSERT INTO test (i) VALUES (2); INSERT 0 1 # INSERT INTO test (i) VALUES (1); ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey" DETAIL: KEY (i)=(1) already EXISTS.
Of course one can say that since we know constraint name, we can extract the value from SQL – that's true, but there are also multi-row inserts:
# INSERT INTO test (i) SELECT generate_series(1,10); ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey" DETAIL: KEY (i)=(1) already EXISTS.
It works the same way when we add index/constraint to existing table:
# CREATE TABLE test ( i int4 ); CREATE TABLE # INSERT INTO test (i) VALUES (1), (2), (1), (3), (2), (4), (5); INSERT 0 7 # CREATE UNIQUE INDEX q ON test (i); ERROR: could NOT CREATE UNIQUE INDEX "q" DETAIL: KEY (i)=(2) IS duplicated.
It is worth noting that only first violating value is reported – i.e. the fact that we fixed problem with value shown in DETAIL doesn't mean that the index can be created now.
To check for all duplicates you should use a bit more complex query:
# SELECT i FROM test GROUP BY i HAVING COUNT(*) > 1; i --- 1 2 (2 ROWS)
An excellent feature that databases should have implemented decades ago.