CODEWITHSUNDEEP
databasepostgresqlconstraintsunique-constraint
Finslicer
Finslicer

Reputation: 878

rows that violate unique constraints

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

Upvotes: 3

Views: 2994

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

Update

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

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:

Upvotes: 6

Quassnoi
Quassnoi

Reputation: 425371

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.

Upvotes: 5

Related Questions