Waiting for 8.5 – better messages for unique violation

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)

One thought on “Waiting for 8.5 – better messages for unique violation”

  1. An excellent feature that databases should have implemented decades ago.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.