I guess it comes back to the semantics of NULL. As has been pointed out in
many a database course, what we mean by NULL changes, and how we want to
use NULL changes on circumstances.
Normally, when I am comparing rows, I do want NULL <> NULL. In
this specific instance, no value has been assigned to the specific
column for this row, so NULL is appropriate. However, there are cases
where I am trying to explicitely test for existence of a specific row
in the table, and in this case, I _do_ want a NULL == NULL type of
comparison. I could try and specify a dummy value (in this case, I could
put in -1), but then I am trying to create a second class of NULLs, and
this is usually not considered good design.
Note that as a prime example of how postgresql itself is not "consistent"
(in the strictest sense) is GROUP BY which treats NULL == NULL
(interesting side bar, is there a way to cause GROUP BY to treat NULLs as
not equal to each other?). In a theoretical question, how is this
justified if NULL should not equal to NULL (other than "it is in the
spec")?
Also, is there a particular reason for not having a strict equality
operator (or is it simply because it is not in the specification)?
Performance? No support from the back-end? Something else?
Regards,
Ed
On Wed, 15 Oct 2003, Tom Lane wrote:
> Edmund Dengler <edmundd@eSentire.com> writes:
> > ... I have no real choice in this as there is no way to specify that
> > NULL == NULL.
>
> The conventional wisdom on this is that if you think you need NULL ==
> NULL to yield true, then you are misusing NULL, and you'd better
> reconsider your data representation. The standard semantics for NULL
> really do not support any other interpretation of NULL than "I don't
> know what this value is". If you are trying to use NULL to mean
> something else, you will face nothing but misery. Choose another
> representation for whatever you do mean.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
