Re: plpgsql help - nested loops - Mailing list pgsql-interfaces

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.


On Tuesday 02 January 2001 18:20, Cedar Cox wrote:

> > I'm trying to compare the fields of two loop record variables.  The loops
> are nested, the compare is done inside the inner one.  If given this:
>
>   raise notice ''sloop.serialnumber=%, rloop.serialnumber=%'',
>     sloop.serialnumber, rloop.serialnumber;
>
>   if sloop.serialnumber=rloop.serialnumber then
>     raise notice '' SN match'';
>   else
>     raise notice '' SN mis-match'';
>   end if;
>
> the output is
>
>   NOTICE:  sloop.serialnumber=<NULL>, rloop.serialnumber=<NULL>
>   NOTICE:   SN mis-match
>
> However, if you change the comparison to
>
>   if sloop.serialnumber=rloop.serialnumber or
>     (sloop.serialnumber=null and rloop.serialnumber=null) then
>     raise notice '' SN match'';
>   else
>     raise notice '' SN mis-match'';
>   end if;
>
> everything is fine.
>
> Question: does null=null evaluate to true, false, or null?  If in psql you
> do SELECT null=null; it returns true.  Am I missing something?  Attached
> is the full trigger code..
>
> Thanks,
> -Cedar

----------------------------------------
Content-Type: TEXT/PLAIN; name="matchitems"
Content-Transfer-Encoding: BASE64
Content-Description: 
----------------------------------------

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------