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