Thread: How does PostgreSQL treat null values in unique composite constraints???

I have the following table with a unique constraint.

create table testt (
id int,
aa int,
bb int,
constraint pk_testt primary key (id),
constraint cons_testt unique (aa,bb));

Let's say I insert the following:

insert into testt values (1,2,null);
insert into testt values (2,2,null);

I thought this should fail because of the unique
constraint, but it doesn't.  How does PostgreSQL view
the null values in this constraint?  Will someone
explain why I am allowed to make this insertion?

TIA.

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com


Re: How does PostgreSQL treat null values in unique composite

From
Stephan Szabo
Date:
On Tue, 8 Apr 2003, P G wrote:

> I have the following table with a unique constraint.
>
> create table testt (
> id int,
> aa int,
> bb int,
> constraint pk_testt primary key (id),
> constraint cons_testt unique (aa,bb));
>
> Let's say I insert the following:
>
> insert into testt values (1,2,null);
> insert into testt values (2,2,null);
>
> I thought this should fail because of the unique
> constraint, but it doesn't.  How does PostgreSQL view
> the null values in this constraint?  Will someone
> explain why I am allowed to make this insertion?

Two NULL values still satisfy a unique constraint.  A unique constraint is
defined in terms of the unique predicate.

From the unique predicate:
"If there are no two rows T such that the value of each column in one row
is non-null and is equal to the value of the corresponding column in the
other row according to Subclause 8.2, "<comparison predicate>", then the
result of the <unique predicate> is true; otherwise the result of the
<unique predicate> is false".


Re: How does PostgreSQL treat null values in unique composite

From
Michael Brusser
Date:
I suppose null is 'absence of value' rather than any
value (like an empty string) that can be compared with any
other value for uniqueness.
Mike.


> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of P G
> Sent: Tuesday, April 08, 2003 2:14 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] How does PostgreSQL treat null values in unique
> composite constraints???
>
>
> I have the following table with a unique constraint.
>
> create table testt (
> id int,
> aa int,
> bb int,
> constraint pk_testt primary key (id),
> constraint cons_testt unique (aa,bb));
>
> Let's say I insert the following:
>
> insert into testt values (1,2,null);
> insert into testt values (2,2,null);
>
> I thought this should fail because of the unique
> constraint, but it doesn't.  How does PostgreSQL view
> the null values in this constraint?  Will someone
> explain why I am allowed to make this insertion?
>
> TIA.
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms, and more
> http://tax.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


Re: How does PostgreSQL treat null values in unique composite

From
P G
Date:
Where can I find the documentation cited for the
"unique predicate" mentioned below?

TIA.

--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
>
> On Tue, 8 Apr 2003, P G wrote:
>
> > I have the following table with a unique
> constraint.
> >
> > create table testt (
> > id int,
> > aa int,
> > bb int,
> > constraint pk_testt primary key (id),
> > constraint cons_testt unique (aa,bb));
> >
> > Let's say I insert the following:
> >
> > insert into testt values (1,2,null);
> > insert into testt values (2,2,null);
> >
> > I thought this should fail because of the unique
> > constraint, but it doesn't.  How does PostgreSQL
> view
> > the null values in this constraint?  Will someone
> > explain why I am allowed to make this insertion?
>
> Two NULL values still satisfy a unique constraint.
> A unique constraint is
> defined in terms of the unique predicate.
>
> From the unique predicate:
> "If there are no two rows T such that the value of
> each column in one row
> is non-null and is equal to the value of the
> corresponding column in the
> other row according to Subclause 8.2, "<comparison
> predicate>", then the
> result of the <unique predicate> is true; otherwise
> the result of the
> <unique predicate> is false".
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com


Re: How does PostgreSQL treat null values in unique composite

From
P G
Date:
Also, the "unique predicate" only explains what it
means to be unique, but it says nothing about how null
values are treated.  Will someone explain what the
value of null is?

TIA.

--- P G <pg_dba@yahoo.com> wrote:
> Where can I find the documentation cited for the
> "unique predicate" mentioned below?
>
> TIA.
>
> --- Stephan Szabo <sszabo@megazone23.bigpanda.com>
> wrote:
> >
> > On Tue, 8 Apr 2003, P G wrote:
> >
> > > I have the following table with a unique
> > constraint.
> > >
> > > create table testt (
> > > id int,
> > > aa int,
> > > bb int,
> > > constraint pk_testt primary key (id),
> > > constraint cons_testt unique (aa,bb));
> > >
> > > Let's say I insert the following:
> > >
> > > insert into testt values (1,2,null);
> > > insert into testt values (2,2,null);
> > >
> > > I thought this should fail because of the unique
> > > constraint, but it doesn't.  How does PostgreSQL
> > view
> > > the null values in this constraint?  Will
> someone
> > > explain why I am allowed to make this insertion?
> >
> > Two NULL values still satisfy a unique constraint.
>
> > A unique constraint is
> > defined in terms of the unique predicate.
> >
> > From the unique predicate:
> > "If there are no two rows T such that the value of
> > each column in one row
> > is non-null and is equal to the value of the
> > corresponding column in the
> > other row according to Subclause 8.2, "<comparison
> > predicate>", then the
> > result of the <unique predicate> is true;
> otherwise
> > the result of the
> > <unique predicate> is false".
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms,
> and more
> http://tax.yahoo.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com


Re: How does PostgreSQL treat null values in unique composite

From
Stephan Szabo
Date:
On Tue, 8 Apr 2003, P G wrote:

> > Where can I find the documentation cited for the
> > "unique predicate" mentioned below?
That's the SQL92 draft, although I believe the SQL99 text is effectively
the same.  The sections are 11.7 (for the definition of the constraint
in terms of the predicate) and 8.9 for the predicate itself (General Rule
2 specifically)

> Also, the "unique predicate" only explains what it
> means to be unique, but it says nothing about how null
> values are treated.  Will someone explain what the
> value of null is?

It isn't a precise value.  It's a holder for unknown.  It's unknown
whether it's equal or not equal to any other value (including another
NULL), so 1 = NULL returns unknown, NULL=NULL returns unknown, 1!=NULL
returns unknown.


Re: How does PostgreSQL treat null values in unique

From
Robert Treat
Date:
There's a good run down of NULL's at
http://techdocs.postgresql.org/guides/BriefGuideToNulls

Robert Treat

On Tue, 2003-04-08 at 15:48, P G wrote:
> Also, the "unique predicate" only explains what it
> means to be unique, but it says nothing about how null
> values are treated.  Will someone explain what the
> value of null is?
>