The following bug has been logged online: Bug reference: 5005 Logged by: Dean Schulze Email address: dean.w.schu...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
