Re: Unique Index

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> Or (perhaps better yet, violating trichotomy) ...
> If <Some_column> has a null numeric value, then ALL of the following are
> FALSE for that case:

> Some_column < 0
> Some_column > 0
> Some_column = 0
> Some_column <> 0 // This is the one that many find surprising
> Some_column <= 0
> Some_column >= 0

It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't. "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.

> Even at that, I think that being able to insert more than one null value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

Direct your complaints to the ISO SQL standards committee.

regards, tom lane