Re: unique constraint with a null column?

try something like this

jim=# create table a (a text,b text, c text);
CREATE TABLE
jim=# create unique index a_idx on a(a,b,(coalesce(c,'*** NULL IS HERE ***')));
CREATE INDEX

jim=# insert into a values ('a','b','c');
INSERT 413272154 1
jim=# insert into a values ('a','b',null);
INSERT 413272155 1
jim=# insert into a values ('a','b',null);
ERROR: duplicate key violates unique constraint "a_idx"
jim=# \d a
Table "public.a"
Column | Type | Modifiers
--------+------+-----------
a | text |
b | text |
c | text |
Indexes:
"a_idx" unique, btree (a, b, (COALESCE(c, '*** NULL IS HERE ***'::text)))

---------- Original Message -----------
From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: CSN <cool_screen_name90001(at)yahoo(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Fri, 30 Dec 2005 15:41:33 -0600
Subject: Re: [GENERAL] unique constraint with a null column?

> On Fri, Dec 30, 2005 at 13:30:40 -0800,
> CSN <cool_screen_name90001(at)yahoo(dot)com> wrote:
> > I have three columns, and one of them can be null. I'd
> > like to create a unique constraint across all three
> > columns and allow only one null value. e.g.
> >
> > a|b|c
> > abc|123|null
> > abc|123|null # not allowed
> > abc|456|null
> > abc|456|987
> > abc|456|876
> > def|456|null
> > def|456|null # not allowed
> >
> > Currently, the 'not allowed' lines are allowed.
>
> That is how 'unique' constraints are supposed to work. One possible
> solution is to use some normal value instead of 'NULL' to represent
> that fact.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------