Обсуждение: BUG #5005: unique constraint considers nulls to be equal

The following bug has been logged online:

Bug reference:      5005
Logged by:          Dean Schulze
Email address:      dean.w.schulze@comcast.net
PostgreSQL version: 8.3.7
Operating system:   Win XP
Description:        unique constraint considers nulls to be equal
Details:

When I apply this constraint it fails because there are records that will
violate the new constraint:

ALTER TABLE table1 ADD CONSTRAINT unique_county_year_idnumber UNIQUE
(county, year, idnumber);


Some of the records it fails on have null for idnumber.  According to the
documentation section 5.3.3 nulls should not violate a unique constraint:

However, two null values are not considered equal in this comparison. That
means even in the presence of a unique constraint it is possible to store
duplicate rows that contain a null value in at least one of the constrained
columns.

Here are the duplicate records that violate this constraint:

select county, year, idnumber, count(0) from table1 group by county, year,
idnumber having (count(0) > 1) order by county, count(0);


"county","year","idnumber","count"
"AD",2009,"A57504",2
"AD",2009,"A58395",2
"AD",2009,"A58286",2
"AD",2009,"A58299",2
"AD",2009,"",5
"AR",2009,"2148.0",2
"AR",2009,"20.0",2
"BD",2009,"22497.0",2
"BD",2009,"22499.0",2
"BD",2009,"",5
"DS",2009,"1426",2
"DS",2009,"1443",2
"DV",2009,"3237",2
"DV",2009,"1775.0",2
"DV",2009,"3202",2
"DV",2009,"",3
"EL",2009,"",3
"JF",2009,"J01718",2
"LR",2009,"606.0",2
"LR",2009,"",4

Dean Schulze wrote:

> Here are the duplicate records that violate this constraint:
>
> select county, year, idnumber, count(0) from table1 group by county, year,
> idnumber having (count(0) > 1) order by county, count(0);
>
>
> "county","year","idnumber","count"
> "AD",2009,"A57504",2
> "AD",2009,"A58395",2
> "AD",2009,"A58286",2
> "AD",2009,"A58299",2
> "AD",2009,"",5

What you have here seems to be an empty string, which is not the same as
a NULL value.  True NULLs behave as expected.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support