Re: Table Constraints with NULL values - Mailing list pgsql-sql

"David Allardyce" <dave@pod13.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
theunique columns. In        addition, if the unique constraint was defined with PRIMARY KEY,        then it requires
thatnone 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
equalto the value of the cor-           responding column in the other row according to Subclause 8.2,
"<comparisonpredicate>", then the result of the <unique predi-           cate> is true; otherwise, the result of the
<uniquepredicate>           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
compareequal 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
isnever unknown.
 
        i) duplicate: Two or more values or rows are said to be duplicates           (of each other) if and only if
theyare 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