Skip site navigation (1) Skip section navigation (2)

Re: unique constraint with a null column?

From: "Jim Buttafuoco" <jim(at)contactbda(dot)com>
To: jim(at)contactbda(dot)com, 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>
Subject: Re: unique constraint with a null column?
Date: 2005-12-30 22:06:07
Message-ID: 20051230220526.M55838@contactbda.com (view raw or flat)
Thread:
Lists: pgsql-general
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 -------


In response to

pgsql-general by date

Next:From: Tom LaneDate: 2005-12-30 22:30:19
Subject: Re: unique constraint with a null column?
Previous:From: Jim ButtafuocoDate: 2005-12-30 22:02:48
Subject: Re: unique constraint with a null column?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group