Re: [SQL] multi-column unique constraints with nullable columns

[Mikey]

It may be possible to bypass the NULL != NULL by using coalesce.  Here
is an example:

<BEGIN SQL>

create table foo2 (
a integer not null,
b integer not null,
c integer null,
UNIQUE (a,b,c)
);

create function foo2_unique_func() RETURNS trigger AS '
DECLARE
        isfound integer = 0;
BEGIN
       isfound = (select count(*) from foo2 where
(new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select
a,b,coalesce(c::TEXT,''EmPtY'')
from foo2));
        RAISE NOTICE ''isfound: %'', isfound;
       IF isfound > 0  THEN
               RAISE EXCEPTION ''Columns a,b,c Must Be Unique values
(%,%,%)'', new.a, new.b, new.c;
       ELSE
               RETURN NEW;
       END IF;
END; '  language 'plpgsql';

CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2
   FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func();


insert into foo2 values (1,300, null);
insert into foo2 values (1,300, null);

select * from foo2;
select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in
(select a,b,coalesce(c::TEXT,'EmPtY') from foo2);

drop table foo2 cascade;
drop function foo2_unique_func() cascade;

<END SQL>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]