Someone else suggested using coalesce to me as well. Isn't your function equivilant to mine? If so, I think the most
elegantof these solutions is closer to the one I proposed (with Stephen's modification). Unfortunately, we've still got
theconcurrency problems that Stephen pointed out.
I sure would like the ability to affect the UNIQUE constraint's behavior to support this. Although, I'm probably in the
minorityand it would obviously be un-portable.
I think my plan will actually be to plug leaks in the domain layer and move toward non-nullable natural keys. I was
hopingto find something better, but I frankly don't have the experience in house to write contstraints I'll have
confidencein.
Thanks for the tip!
Phill
-----Original Message-----
From: Mikey [mailto:mikeboscia@gmail.com]
Sent: Thu 5/5/2005 12:03 PM
To: Tornroth, Phill
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] multi-column unique constraints with nullable columns
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
''Columnsa,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>
