Skip site navigation (1) Skip section navigation (2)

Re: plpgsql help - nested loops

From: Cedar Cox <cedarc(at)visionforisrael(dot)com>
To: "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: plpgsql help - nested loops
Date: 2001-01-03 20:13:14
Message-ID: Pine.LNX.4.21.0101032201050.1686-100000@nanu.visionforisrael.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
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



In response to

pgsql-interfaces by date

Next:From: Tom LaneDate: 2001-01-03 20:36:49
Subject: Re: [SQL] Numeric and money
Previous:From: Cedar CoxDate: 2001-01-03 20:00:33
Subject: Re: ODBC - Invalid protocol character

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group