Re: Table Constraints with NULL values

"David Allardyce" <dave(at)pod13(dot)net> writes:
> However, shouldn't any values that are not NULL violate the constraint if
> the same values exist already?

No. Postgres is conforming to the SQL standard in this. SQL92 saith
in section 4.10:

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. In
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

There's a slightly different statement in the definition of the UNIQUE
predicate, section 8.9:

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.

AFAICT the intent is that all the comparison columns must be non-null
(and equal) for two rows to be considered to violate the uniqueness
constraint.

The standard *does* use the concept that you are after: section
3.1 Definitions saith

h) distinct: Two values are said to be not distinct if either:
both are the null value, or they compare equal according to
Subclause 8.2, "<comparison predicate>". Otherwise they are
distinct. Two rows (or partial rows) are distinct if at least
one of their pairs of respective values is distinct. Otherwise
they are not distinct. The result of evaluating whether or not
two values or two rows are distinct is never unknown.

i) duplicate: Two or more values or rows are said to be duplicates
(of each other) if and only if they are not distinct.

which is terminology that they carefully refrain from using in defining
uniqueness constraints. Had they meant what you want the behavior to
be, ISTM they'd have defined uniqueness constraints by saying "all the
rows must be distinct".

> -- Excerpt from the Postgres CREATE TABLE documentation ---
> ...
> UNIQUE Constraint
> ...
> The column definitions of the specified columns do not have to include a NOT
> NULL constraint to be included in a UNIQUE constraint. Having more than one
> null value in a column without a NOT NULL constraint, does not violate a
> UNIQUE constraint. (This deviates from the SQL92 definition, but is a more
> sensible convention. See the section on compatibility for more details.)

I believe that this statement is wrong, and that we do follow the spec.
There have been a number of arguments about this in the past though...
evidently whoever touched this doc page last had the opposite opinion.

regards, tom lane