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@2xlp.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general