From: Robert B. Easter <reaster@comptechnews.com>
To: Cedar Cox <cedarc@visionforisrael.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: plpgsql help - nested loops
Date: Wed, 3 Jan 2001 09:16:40 -0500
Message-ID: <0101030916400A.09559@comptechnews> (raw)
In-Reply-To: <Pine.LNX.4.21.0101030107140.28193-200000@nanu.visionforisrael.com>
References: <Pine.LNX.4.21.0101030107140.28193-200000@nanu.visionforisrael.com>
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/ ------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: reaster@comptechnews.com, cedarc@visionforisrael.com
Subject: Re: plpgsql help - nested loops
In-Reply-To: <0101030916400A.09559@comptechnews>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox