From: Mikey <mikeboscia@gmail.com>
To: Tornroth, Phill <ptornroth@intellidot.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: multi-column unique constraints with nullable columns
Date: Thu, 5 May 2005 12:03:36 -0700
Message-ID: <45caccdc05050512035b897fd3@mail.gmail.com> (raw)
In-Reply-To: <967F41F63A2D10469114F0A19E56B17E365B8F@SIRIUS.intellidot.net>
References: <967F41F63A2D10469114F0A19E56B17E365B8F@SIRIUS.intellidot.net>
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mikeboscia@gmail.com, ptornroth@intellidot.net
Subject: Re: multi-column unique constraints with nullable columns
In-Reply-To: <45caccdc05050512035b897fd3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox