From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | Robert Edwards <bob(at)cs(dot)anu(dot)edu(dot)au>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: uniqueness constraint with NULLs |
Date: | 2009-06-29 14:01:23 |
Message-ID: | 11273.1246284083@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Craig Ringer <craig(at)postnewspapers(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-06-29 14:25:20 | Re: date_trunc should be called date_round? |
Previous Message | Craig Ringer | 2009-06-29 11:32:30 | Re: uniqueness constraint with NULLs |