Edmund Dengler <edmundd@eSentire.com> writes:
> Normally, when I am comparing rows, I do want NULL <> NULL.
No, you still haven't got the point. NULL is not equal to NULL, and
it is not not-equal-to NULL either. The result of the comparison is
NULL, not true or false. This is consistent with the interpretation
of NULL as "I don't know the value". If you don't know what the value
is, you also don't know whether it is equal to some other value.
> Note that as a prime example of how postgresql itself is not "consistent"
> (in the strictest sense) is GROUP BY which treats NULL == NULL
Shrug ... the standard tells us to do that. SQL has never been held up
as a model of consistency.
> Also, is there a particular reason for not having a strict equality
> operator (or is it simply because it is not in the specification)?
The existing operators *are* strict (which is defined as NULL in yields
NULL out). You could build a set of non-strict comparison operators if
you had a mind to. IIRC you would lose some potential hashtable
optimizations, but in the main it would work.
regards, tom lane