2010/5/10 Jonathan Vanasco <postgres@xxxxxxxx>: > -- running pg 8.4 > > i have a table defining geographic locations > > id > lat > long > country_id not null > state_id > city_id > postal_code_id > > i was given a unique index on > (country_id, state_id, city_id, postal_code_id) > > the unique index isn't working as i'd expect it to. i was hoping someone > could explain why: > > in the two records below, only country_id and state_id are assigned ( aside > from the serial ) > > geographic_location_id | coordinates_latitude | coordinates_longitude | > country_id | state_id | city_id | postal_code_id > ------------------------+----------------------+-----------------------+------------+----------+---------+---------------- > 312 | | | > 233 | 65 | | > 443 | | | > 233 | 65 | | > > i was under the expectation that the unique constraint would apply in this > place. > > from the docs: > When an index is declared unique, multiple table rows with equal > indexed values are not allowed. Null values are not considered equal. A > multicolumn unique index will only reject cases where all indexed columns > are equal in multiple rows. NULLs are not considered equal, so you can have an UNIQUE on a column with multiple times a NULL. You migth want to explicitely add a 'NOT NULL' to your columns here. > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Cédric Villemain -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
