Re: question about unique indexes

Use unique index as follows:

create unique index unq_idx on table_name (coalesce(country_id,0),
coalesce(state_id,0), coalesce(city_id,0),coalesce(postal_code_id,0) );

On Mon, May 10, 2010 at 6:09 AM, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:

> -- 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.
>
>
> --
> 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
>