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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings