Re: uniqueness constraint with NULLs

Craig Ringer <craig@postnewspapers.com.au> writes:
> On Mon, 2009-06-29 at 15:42 +1000, Robert Edwards wrote:
>> Can anyone suggest a way that I can impose uniqueness on a and b when
>> c is NULL?

> One way is to add an additional partial index on (a,b):
> CREATE INDEX bobtest_ab_unique ON bobtest(a,b) WHERE (c IS NULL);

That's the way I'd suggest; unlike the other proposal, it doesn't make
any assumptions about datatypes and it doesn't require there to be a
special non-null value that won't be a real data value.

> ... however, if you want to do the same sort of thing for all
> permutations (a, null, null), (b, null, null), (c, null, null), (a, b,
> null), (a, c, null), (b, c, null), (a, b, c)that'll be a LOT of indexes.

... yeah.  So one answer that definitely requires consideration is
"you have misdesigned your data representation; do not try to use NULL
this way".

> In that case you might be better off just using a trigger function like
> (untested but should be about right):

This trigger has race conditions: it will fail to prevent concurrent
insertion of rows that you would like to have conflict.  I think it
does the wrong thing for the UPDATE case too, though that is fixable.
The race condition isn't.
        regards, tom lane