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.