Thread: question about unique indexes

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

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

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 10 May 2010, at 2:09, Jonathan Vanasco wrote:

> i was given a unique index on
>     (country_id, state_id, city_id, postal_code_id)

> 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
notconsidered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple
rows.


As the docs state and as others already mentioned, "Null values are not considered equal".

You're about to encounter an interesting problem. You have several optional foreign keys, so they have to be declared
nullable,yet your constraints are such that you can't enforce uniqueness because nulls can't be compared. 

You could (as mentioned by Al Rumman) create a unique index using coalesce() for each nullable column, but that index
wouldn'tbe usable for normal queries - your query expressions won't match the indexes expressions ('WHERE city_id=7'
vs.'COALESCE(city_id, 0)=7'). I don't think the query planner sees the similarity between those expressions. 

It is possible to add another index over those columns, without the coalesces, but it would take another time that
amountof disk/memory space and the planner likely wouldn't be aware of the uniqueness of the data in it and hence not
planfor that. 

You could also change all your queries to use coalesce() for each of those columns. Hiding that in a view (maybe an
updatableone so that CRUD operations can use the index too) would be a solution. 

An alternative solution is to actually use 0 for those foreign keys and create a special record for that in the related
tables.It depends on the table definitions how easy that is to do, you don't want to end up with all kinds of
dummy-datain your client application, but you also don't want to remove any constraints that guarantee sensibility of
thedata in those tables. 

None of these solutions are pretty. It should be quite a common problem though, how do people normally solve this?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4be7e01210416358213314!

On May 10, 2010, at 6:29 AM, Alban Hertroys wrote:
> As the docs state and as others already mentioned, "Null values are
> not considered equal".


Ah.  I interpreted that wrong.  I thought it applied to indexes
differently.  I'll have to experiment now...

Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

> [...]
> None of these solutions are pretty. It should be quite a common problem though, how do people normally solve this?

Partial indexes? Doesn't look pretty either though:

| tim=# \d DE_Postcodes
|  Tabelle »public.de_postcodes«
|   Spalte  |   Typ   | Attribute
| ----------+---------+-----------
|  postcode | integer | not null
|  city     | text    | not null
|  suffix   | text    |
|  street   | text    | not null
|  first    | integer |
|  last     | integer |
| Indexe:
|     "de_postcodes_key1" UNIQUE, btree (postcode, city, suffix, street, first, last) WHERE suffix IS NOT NULL AND
firstIS NOT NULL AND last IS NOT NULL 
|     "de_postcodes_key2" UNIQUE, btree (postcode, city, suffix, street, first) WHERE suffix IS NOT NULL AND first IS
NOTNULL AND last IS NULL 
|     "de_postcodes_key3" UNIQUE, btree (postcode, city, suffix, street, last) WHERE suffix IS NOT NULL AND first IS
NULLAND last IS NOT NULL 
|     "de_postcodes_key4" UNIQUE, btree (postcode, city, suffix, street) WHERE suffix IS NOT NULL AND first IS NULL AND
lastIS NULL 
|     "de_postcodes_key5" UNIQUE, btree (postcode, city, street, first, last) WHERE suffix IS NULL AND first IS NOT
NULLAND last IS NOT NULL 
|     "de_postcodes_key6" UNIQUE, btree (postcode, city, street, first) WHERE suffix IS NULL AND first IS NOT NULL AND
lastIS NULL 
|     "de_postcodes_key7" UNIQUE, btree (postcode, city, street, last) WHERE suffix IS NULL AND first IS NULL AND last
ISNOT NULL 
|     "de_postcodes_key8" UNIQUE, btree (postcode, city, street) WHERE suffix IS NULL AND first IS NULL AND last IS
NULL
|     "de_postcodes_postcodecity" btree (postcode, city)

| tim=#

Tim