Re: How does PostgreSQL treat null values in unique composite

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