Re: Unique Index - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Huh?

... the value of EACH COLUMN in one row is NOT NULL and IS EQUAL to ...

In order for values to be equal in SQL, neither one can be null.  For
this condition to hold, it is more than "clear" that at least one row
must contain *NO* *NULL* *VALUES* (that means zero columns in that row
may contain null values).  Since *ALL* columns in the other row must be
EQUAL to the corresponding column in that row, none of them can be null
either.  Therefore, the uniqueness predicate evaluates to false, and
each of the two rows is considered unique compared to the other as soon
as any null value shows up in either row.

There is *no* ambiguity here!

On Jan 20, 2005, at 1:55 PM, Dann Corbit wrote:

> It is clear to me that only allowing a single null value will not
> violate the explanation below.
>
> It would be equally true that allowing multiple null values would not
> violate it.
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Thursday, January 20, 2005 8:03 AM
> To: Greg Stark
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Unique Index
>
> Greg Stark <gsstark@mit.edu> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity
> constraints":
>>>
>>> A unique constraint is satisfied if and only if no two rows in
>>> a table have the same non-null values in the unique columns.
>
>> That's ambiguous. Does it mean no two rows have all non-null columns
> that are
>> all identical? Or does it mean no two rows have columns that excluding
> any
>> null columns are identical.
>
> OK, try the more formal definition in 8.9 <unique predicate>
>
>          2) If there are no two rows in T such that the value of each
> column
>             in one row is non-null and is equal to the value of the
> cor-
>             responding column in the other row according to Subclause
> 8.2,
>             "<comparison predicate>", then the result of the <unique
> predi-
>             cate> is true; otherwise, the result of the <unique
> predicate>
>             is false.
>
> (11.7 defines the UNIQUE constraint in terms of the unique predicate)
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB8AX07aqtWrR9cZoRAlu+AJ0YCv6VEID7MR5BHOf6rRl+gyPeLgCghImV
Um7r0pyp2vfsCVArSOkswMM=
=kzpI
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com