Re: uniqueness constraint with NULLs - Mailing list pgsql-sql

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.