Re: How does PostgreSQL treat null values in unique composite

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