rows that violate unique constraints

Question

I have a postgres 8.3.4 database. A name table exists with a unique constraint on the triplet UNIQ(name, id, age). Somehow there are several rows that have been added to the database that cause violation of this constraint.

My question is how is this possible ? Shouldn't the database have thrown an error when the first row that would violate the constraint was added ?

name : text
id : integer not null (fk to a id table)
age : integer

PostgreSQL 8.3 is no longer supported. If you cannot for some strange reason update to a current version (9.x) then you should at least update to the latest 8.3 version which is 8.3.18. — , Apr 23 '12 at 06:53
Are you sure the names are the same? Have you controlled for trailing white-space? And also, as many people have already commented, you would be wise to upgrade to a current version of PostgreSQL: http://www.postgresql.org/support/versioning/ — Andrew, Apr 24 '12 at 00:51

Erwin Brandstetter · Answer 1 · 2022-12-13T13:31:00.210

Update

Postgres 15 added NULLS NOT DISTINCT for UNIQUE constraints and indexes. See:

Create unique constraint with null columns

Original answer

My guess is you are missing the fact that NULL values are considered to be distinct, i.e., do not conflict with a UNIQUE constraint. If you enter:

(NULL, 1, 20)

for (name, id, age) multiple times, you get no unique violation. Two NULL values are not considered "the same" (i.e. "distinct").

You can either set all involved columns NOT NULL (after replacing NULL values with dummy values).

Or you can implement additional partial indexes to cover NULLs (after cleaning up "dupes" with NULL). For instance, if you need to cover the name column for NULLs:

CREATE UNIQUE INDEX tbl_id_age_name_null_idx ON my_table (id, age)
WHERE name IS NULL;

Then you can have:

  ('pete', 1, 20)
, ('jane', 1, 20) 
, (NULL  , 1, 20)

for (name, id, age) in your table, but none of these a second time. See:

PostgreSQL multi-column unique constraint and NULL values

@Finslicer: You might want to edit your question. The information there does not reflect what you are commenting here. — Erwin Brandstetter, Apr 23 '12 at 11:43

Quassnoi · Answer 2 · 2012-04-23T06:38:00.810

It's hardly feasible.

Most probably, you have extra spaces in names or something like this.

Please post the exact table definition.

Also, please run this query:

SELECT  q2.*
FROM    (
        SELECT  name, id, age
        FROM    mytable
        GROUP BY
                name, id, age
        HAVING  COUNT(*) > 1
        ) q
JOIN    mytable q2
ON      (q2.name, q2.id, q2.age) IS NOT DISTINCT FROM (q.name, q.id, q.age)

and post some output returned here.

rows that violate unique constraints

2 Answers2

Update

Original answer