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
