Re: question about unique indexes

2010/5/10 Jonathan Vanasco <postgres(at)2xlp(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Cédric Villemain