2010/5/10 Jonathan Vanasco <postgres@2xlp.com>:
> -- 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Cédric Villemain
