Re: How does PostgreSQL treat null values in unique composite - Mailing list pgsql-admin

From Stephan Szabo
Subject Re: How does PostgreSQL treat null values in unique composite
Date
Msg-id 20030408111533.L52961-100000@megazone23.bigpanda.com
Whole thread Raw
In response to How does PostgreSQL treat null values in unique composite constraints???  (P G <pg_dba@yahoo.com>)
Responses Re: How does PostgreSQL treat null values in unique composite
List pgsql-admin
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".


pgsql-admin by date:

Previous
From: P G
Date:
Subject: How does PostgreSQL treat null values in unique composite constraints???
Next
From: Michael Brusser
Date:
Subject: Re: How does PostgreSQL treat null values in unique composite