X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D8FBB53866
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  5 May 2005 16:03:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 38121-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  5 May 2005 19:03:36 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.199])
	by svr1.postgresql.org (Postfix) with ESMTP id F24745338F
	for <pgsql-sql@postgresql.org>; Thu,  5 May 2005 16:03:35 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 68so865946wri
	for <pgsql-sql@postgresql.org>; Thu, 05 May 2005 12:03:36 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=swosJJBCdFsV7Ym1uTGywjcuREJfzRBoNogAhMQeRytdCLR7kVfVIBMub3EPpD1+uNez5JeX6scSnAEUQzwKy2ch3B960f6zMfBu18ap7flEhtudgnlChpqXWEXylQOZxF8qgyFZJVBnXLDtOXW3uXAoEnVuqx3s7dxkOrP7vww=
Received: by 10.54.41.20 with SMTP id o20mr237603wro;
	Thu, 05 May 2005 12:03:36 -0700 (PDT)
Received: by 10.54.41.37 with HTTP; Thu, 5 May 2005 12:03:36 -0700 (PDT)
Message-ID: <45caccdc05050512035b897fd3@mail.gmail.com>
Date: Thu, 5 May 2005 12:03:36 -0700
From: Mikey <mikeboscia@gmail.com>
Reply-To: Mikey <mikeboscia@gmail.com>
To: "Tornroth, Phill" <ptornroth@intellidot.net>
Subject: Re: multi-column unique constraints with nullable columns
Cc: pgsql-sql@postgresql.org
In-Reply-To: <967F41F63A2D10469114F0A19E56B17E365B8F@SIRIUS.intellidot.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <967F41F63A2D10469114F0A19E56B17E365B8F@SIRIUS.intellidot.net>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.621 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, RCVD_BY_IP, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200505/48
X-Sequence-Number: 21361

It may be possible to bypass the NULL !=3D 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 =3D 0;
BEGIN
       isfound =3D (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>