Re: Unique Index

The ISO SQL Standard does not even define an index, and so any index is
an extension to the standard (though primary keys and foreign keys imply
them).

At least in the SQL Standard that I have (ANSI/ISO/IEC 9075-1-1999 and
related documents) has no definition of an index. Perhaps the newer
version contains such a definition.

So, in the creation of an extension, I think it is up to the programmer
to do whatever is best.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, January 19, 2005 4:18 PM
To: Dann Corbit
Cc: vhikida(at)inreach(dot)com; J. Greenlees; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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