Re: unique constraint with a null column?

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