Re: question about unique indexes

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