UNIQUE not honoured for NULL

David Newall wrote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t(i integer UNIQUE);
INSERT INTO t VALUES (null);
INSERT INTO t VALUES (null);
SELECT coalesce(i,-999) FROM t;
coalesce
----------
-999
-999
(2 rows)

NULL = NULL is not true, so the rows are not equal.

On Tue, 6 Jul 2004, David Newall wrote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t(i integer UNIQUE);
INSERT INTO t VALUES (null);
INSERT INTO t VALUES (null);
SELECT coalesce(i,-999) FROM t;
coalesce
----------
-999
-999
(2 rows)

NULL values are explicitly allowed to be duplicated in unique constraints
by spec AFAICS. The unique constraint is defined in terms of the unique
predicate which says: "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
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."

David Newall wrote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t(i integer UNIQUE);
INSERT INTO t VALUES (null);
INSERT INTO t VALUES (null);
SELECT coalesce(i,-999) FROM t;
coalesce
----------
-999
-999
(2 rows)

As someone says NULL = NULL is false
so that two rows are different. If you want enforce
only one null value for that column you have to write
you own trigger.

Regards
Gaetano Mendola

On Mon, Jul 12, 2004 at 14:47:34 +0200,
Gaetano Mendola <mendola@bigfoot.com> wrote:

As someone says NULL = NULL is false

As someone else pointed out, NULL = NULL is NULL which is not TRUE.

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

Bruno Wolff III wrote:

| On Mon, Jul 12, 2004 at 14:47:34 +0200,
| Gaetano Mendola <mendola@bigfoot.com> wrote:
|
|>As someone says NULL = NULL is false
|
|
| As someone else pointed out, NULL = NULL is NULL which is not TRUE.

Yes, that it's better.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFA8qXV7UpzwH2SGd4RAuENAKCxQGdRV59L9f6fqRiSm4qHC3DOJACeJ/QL
P2u0s/SyFrBalPIgVXoOClI=
=u0Ny
-----END PGP SIGNATURE-----

Gaetano Mendola wrote:

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

Bruno Wolff III wrote:

| On Mon, Jul 12, 2004 at 14:47:34 +0200,
| Gaetano Mendola <mendola@bigfoot.com> wrote:
|
|>As someone says NULL = NULL is false
|
|
| As someone else pointed out, NULL = NULL is NULL which is not TRUE.

Yes, that it's better.

Still not precise.

NULL = NULL
is NULL (which is neither TRUE nor FALSE)

while
(NULL = NULL) IS NULL
is TRUE...

Regards,
Andreas