Re: unique constraint with a null column?

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.

thanks
csn

__________________________________
Yahoo! for Good - Make a difference this year.
http://brand.yahoo.com/cybergivingweek2005/

--- Bruno Wolff III <bruno(at)wolff(dot)to> wrote:

> 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.
>

I know ;). 'c' is actually 'parent_id' with a f/k
contraint, so something like '0' wouldn't work. Hmm,
IIRC indexes can have WHERE clauses - perhaps I can
create a unique index that way...

csn

__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com

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.

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 -------

you could also use a big number to if the column is a int/int8/float/numeric.

Jim

---------- Original Message -----------
From: "Jim Buttafuoco" <jim(at)contactbda(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)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 17:02:48 -0500
Subject: Re: [GENERAL] 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 -------
>
> ---------------------------(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
------- End of Original Message -------

CSN <cool_screen_name90001(at)yahoo(dot)com> writes:
> 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.

UNIQUE won't do that for you, but you could brute force it with a
unique constraint plus a check constraint along the lines of
(a notnull AND b notnull) OR
(a notnull AND c notnull) OR
(b notnull AND c notnull)

However, the real question is whether you think that "unique"
means "no more than one instance of x,y,NULL". If that's what
you mean then UNIQUE isn't going to enforce it ...

regards, tom lane