PostgreSQL Discussion - unique constraint with a null column?

Author

unique constraint with a null column?

CSN

2005-12-30, 8:23 pm

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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruno Wolff III

2005-12-30, 8:23 pm

On Fri, Dec 30, 2005 at 13:30:40 -0800,
CSN < cool_screen_name9000
1@yahoo.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

CSN

2005-12-30, 8:23 pm

--- Bruno Wolff III <bruno@wolff.to> wrote:

> On Fri, Dec 30, 2005 at 13:30:40 -0800,
> CSN < cool_screen_name9000
1@yahoo.com> wrote:
> I'd
> three
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jim Buttafuoco

2005-12-30, 8:23 pm

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@wolff.to>
To: CSN < cool_screen_name9000
1@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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_name9000
1@yahoo.com> wrote:
>
> 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

Jim Buttafuoco

2005-12-30, 8:23 pm

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

Jim

---------- Original Message -----------
From: "Jim Buttafuoco" <jim@contactbda.com>
To: Bruno Wolff III <bruno@wolff.to>, CSN < cool_screen_name9000
1@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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@wolff.to>
> To: CSN < cool_screen_name9000
1@yahoo.com>
> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Sent: Fri, 30 Dec 2005 15:41:33 -0600
> Subject: Re: [GENERAL] unique constraint with a null column?
>
> ------- 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 -------

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Tom Lane

2005-12-30, 8:23 pm

CSN < cool_screen_name9000
1@yahoo.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links