Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNIQUE not honoured for NULL

UNIQUE not honoured for NULL

From
David Newall
Date:
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)

Re: UNIQUE not honoured for NULL

From
Peter Eisentraut
Date:
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.

Re: UNIQUE not honoured for NULL

From
Stephan Szabo
Date:
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."

Re: UNIQUE not honoured for NULL

From
Gaetano Mendola
Date:
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

Re: UNIQUE not honoured for NULL

From
Bruno Wolff III
Date:
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.

Re: UNIQUE not honoured for NULL

From
Gaetano Mendola
Date:
-----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-----

Re: UNIQUE not honoured for NULL

From
Andreas Pflug
Date:
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