To: Craig Ringer <craig@postnewspapers.com.au>
cc: Robert Edwards <bob@cs.anu.edu.au>, pgsql-sql@postgresql.org
Subject: Re: uniqueness constraint with NULLs 
In-reply-to: <1246275150.11346.27.camel@ayaki> 
References: <4A485431.3080707@cs.anu.edu.au> <1246275150.11346.27.camel@ayaki>
Comments: In-reply-to Craig Ringer <craig@postnewspapers.com.au>
	message dated "Mon, 29 Jun 2009 19:32:30 +0800"
Date: Mon, 29 Jun 2009 10:01:23 -0400
Message-ID: <11273.1246284083@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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