Re: question about unique indexes - Mailing list pgsql-general

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!