Message-ID: <4A495AB4.4000605@cs.anu.edu.au>
Date: Tue, 30 Jun 2009 10:22:12 +1000
From: Robert Edwards <bob@cs.anu.edu.au>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: pgsql-sql@postgresql.org
References: <4A485431.3080707@cs.anu.edu.au> <1246275150.11346.27.camel@ayaki>
In-Reply-To: <1246275150.11346.27.camel@ayaki>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: uniqueness constraint with NULLs


Thanks for all these great ideas!

Craig Ringer wrote:
> 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);

Would this be in addition to a unique constraint on (a, b, c) (for the
cases where c is not null)?

> 
> ... 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.
> 

In the real app. a and b are not null ints and c is a date. The date
indicates if and when a row has expired (there are other columns in the
table). I am trying to avoid having separate columns for the "if" and
the "when" of the expiry.

One alternate would be to use a date way off into the future (such as
the famous 9/9/99 case many COBOL programmers used back in the 60's...)
and to test on expired < now ().

Another option is to use a separate shadow table for the expired rows
and to use a trigger function to "move" expired rows to that shadow
table. Then need to use UNION etc. when I need to search across both
current and expired rows.

> In that case you might be better off just using a trigger function like
> (untested but should be about right):
> 
> CREATE OR REPLACE FUNCTION bobtest_unique_trigger() RETURNS trigger AS
> $$
> declare
>   conflicting_id integer;
> begin
>   if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
>     select into conflicting_id from bobtest
>     where (NOT new.a IS DISTINCT FROM a)
>       and (NOT new.b IS DISTINCT FROM b)
>       and (NOT new.c IS DISTINCT FROM c);
>     if found then
>       raise exception 'Unique violation in bobest: inserted row
> conflicts with row id=%',conflicting_id;
>     end if;
>   end if;
> end;
> $$ LANGUAGE 'plpgsql';
> 
> ... which enforces uniqueness considering nulls.

I am "guessing" that the "functional index" that Andreas Kretschmer
proposed would be a lot "lighter-weight" than a full trigger. This
table will get quite a bit of insert activity and some update activity
on the "c" (expired) column, so this uniqueness index will get
exercised quite a lot. I am concerned that this could cause performance 
issues with a heavier-weight trigger function (but have no empirical
data to back up these concerns...).

> 
>> In the real app., c is a date field and I require it to be NULL for
>> some rows.
> 
> Oh. Er, In that case, the partial unique index is your best bet (but 'a'
> and 'b' should ne NOT NULL, right).

Right - see above.

> 
>> in case I am missing some other solution that
>> doesn't involve the use of triggers etc.
> 
> Sometimes a trigger is the right solution.
> 

Yep - I have many of those in other places as well.

Cheers,

Bob Edwards.