Re: UNIQUE not honoured for NULL

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