On Wed, 3 Jan 2001, Robert B. Easter wrote:
> See:
> http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic
>
> I think it might clarify the situation. If you find any errors on the page,
> please let me know.
>
> Ordinary equality comparions (=,>=,<=) between a NULL and anything else
> always results in NULL, normally. NULL in boolean comparisons, IS, IS NOT,
> AND, and OR can give different results. Your equals comparison was always
> giving NULL when comparing the two fields together directly. You were
> getting a confusing result when comparing equality of one field directly with
> NULL, which normally would be NULL too except that PostgreSQL is doing some
> rewriting of the expressing behind your back, changing anything it sees with
> an equality operator and a literal NULL into a boolean comparison "field IS
> NULL" (true if field is NULL) instead of "field = NULL" (normally always NULL
> in the absence of a write you aren't seeing within the database).
>
> I think that is what the deal is. The proper way to check for null, if it is
> a possibility (no NOT NULL constraint), is to use boolean operators (IS, IS
> NOT) explicitly to check.
Ok, makes sense.. This should be, if there isn't, documented somewhere
about the rewriting. So I guess the proper way would be more like the
second example except using 'is':
if sloop.serialnumber=rloop.serialnumber or (sloop.serialnumber is null and rloop.serialnumber is null) then raise
notice'' SN match''; else raise notice '' SN mis-match''; end if;
Of course, I'd use isnull instead. I wish I didn't have to type that
little bit more just to compare two variables.. ;)
Thanks,
-Cedar