From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Robert Edwards <bob@cs.anu.edu.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: uniqueness constraint with NULLs
Date: Mon, 29 Jun 2009 10:01:23 -0400
Message-ID: <11273.1246284083@sss.pgh.pa.us> (raw)
In-Reply-To: <1246275150.11346.27.camel@ayaki>
References: <4A485431.3080707@cs.anu.edu.au>
<1246275150.11346.27.camel@ayaki>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, craig@postnewspapers.com.au, bob@cs.anu.edu.au
Subject: Re: uniqueness constraint with NULLs
In-Reply-To: <11273.1246284083@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox