Re: Unique Index

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >> Direct your complaints to the ISO SQL standards committee.
>
> > The SQL standard generally treats NULLs as a escape hatch for constraints.

Huh? I thought I was agreeing with you. By "escape hatch" I meant that having
a NULL value in the indexed column allowed multiple records with otherwise
identical values because the NULL compared unequal. Ie, that the unique index
doesn't apply to the record with NULL columns.

> Not for UNIQUE constraints. SQL92 section 4.10 "Integrity constraints":
>
> 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.

That's ambiguous. Does it mean no two rows have all non-null columns that are
all identical? Or does it mean no two rows have columns that excluding any
null columns are identical.

It actually sounds more like the latter to me which would mean Postgres's
interpretation is wrong.

> The short answer to this thread is that the OP is misusing nulls,
> and should pick some non-null value to be his "placeholder".

We're in "violent agreement".

--
greg