Re: question about unique indexes

Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)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 first IS 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 NOT NULL AND last IS NULL
| "de_postcodes_key3" UNIQUE, btree (postcode, city, suffix, street, last) WHERE suffix IS NOT NULL AND first IS NULL AND last IS NOT NULL
| "de_postcodes_key4" UNIQUE, btree (postcode, city, suffix, street) WHERE suffix IS NOT NULL AND first IS NULL AND last IS NULL
| "de_postcodes_key5" UNIQUE, btree (postcode, city, street, first, last) WHERE suffix IS NULL AND first IS NOT NULL AND last IS NOT NULL
| "de_postcodes_key6" UNIQUE, btree (postcode, city, street, first) WHERE suffix IS NULL AND first IS NOT NULL AND last IS NULL
| "de_postcodes_key7" UNIQUE, btree (postcode, city, street, last) WHERE suffix IS NULL AND first IS NULL AND last IS NOT 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