Re: Unique index problem

On 12/20/15 10:18 AM, Andreas Kretschmer wrote:
> test=*# create unique index on foo(a,b,c) where a is not null and b is
> not null and c is not null;
> CREATE INDEX

As you discovered, you'd have to build separate indexes for each of the
nullable fields:

UNIQUE ON (a,b) WHERE c IS NULL
a,c WHERE b IS NULL
b,c WHERE a IS NULL
a WHERE b IS NULL AND c IS NULL
....

That's doable for 3 fields, but obviously gets out of hand really quickly.

I wonder if it would be possible to construct an operator family
(class?) that would accept 2 rows( ie: ROW(a,b,c) ) and treat NULLs as
single values...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com