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 <[EMAIL PROTECTED]>
To: CSN <[EMAIL PROTECTED]>
Cc: "[email protected]" <[email protected]>
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 <[EMAIL PROTECTED]> 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 -------
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
