Thread: NULL != text ?
I was trying this: IF (OLD.value != NEW.value) THEN -- END IF; and couldn't get the condition to evaluate to true at all if OLD.value was NULL. I also tried: IF (OLD.value NOT LIKE NEW.value) THEN -- END IF; with the same result. But this works: IF ((OLD.value is NULL and NEW.value is NOT NULL) or (OLD.value != NEW.value)) THEN -- END IF; So, does NULL != 'abc' always evaluate to false? The manual (http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html) states don't compare NULL values using =, but nothing about using != CSN __________________________________ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/
On Oct 20, 2005, at 15:04 , CSN wrote: > So, does NULL != 'abc' always evaluate to false? The > manual > (http://www.postgresql.org/docs/8.0/interactive/functions- > comparison.html) > states don't compare NULL values using =, but nothing > about using != The SQL standard way of checking for NULL is using IS NULL or IS NOT NULL. NULL is unknown. You can't meaningfully compare with something that is unknown, so you can't use = or <> (or it's alternate spelling !=) to find out if something is NULL. Comparison with NULL on one side of the comparison will result in NULL (*not* FALSE). For a little fun (OK, I have to be a bit of a geek to call it that...) with comparisons, see the end of this email. I do my best to not allow any NULLs in my database schema, i.e., always use NOT NULL in table definitions, (I can't remember the last time I didn't), which neatly avoids this problem entirely :) However, given your schema, I'd try if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value <> NEW.value) or OLD.value IS NULL or NEW.value IS NULL But that's untested and I have a hard time thinking in three-value logic. Hope this helps. Michael Glaesemann grzm myrealbox com test=# select 1 = 1; ?column? ---------- t (1 row) test=# select 1 = 2; ?column? ---------- f (1 row) test=# select (1 <> NULL) IS NULL; ?column? ---------- t (1 row) test=# select (NULL = NULL) IS NULL; ?column? ---------- t (1 row) test=# select (0 <> NULL) IS NULL; ?column? ---------- t (1 row) test=# select (NULL IS NULL); ?column? ---------- t (1 row) test=# select (NULL IS NOT NULL); ?column? ---------- f (1 row)
On Wed, Oct 19, 2005 at 11:04:36PM -0700, CSN wrote: > So, does NULL != 'abc' always evaluate to false? It never evaluates to false -- it evaluates to NULL. http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html The ordinary comparison operators yield null (signifying "unknown") when either input is null. Another way to do comparisons is with the IS DISTINCT FROM construct: expression IS DISTINCT FROM expression For non-null inputs this is the same as the <> operator. However, when both inputs are null it will return false, and when just one input is null it will return true. Thus it effectively acts as though null were a normal data value, rather than "unknown". Examples: test=> SELECT NULL = 'abc'; ?column? ---------- (1 row) test=> SELECT NULL <> 'abc'; ?column? ---------- (1 row) test=> SELECT NULL IS DISTINCT FROM 'abc'; ?column? ---------- t (1 row) test=> SELECT NULL IS DISTINCT FROM NULL; ?column? ---------- f (1 row) -- Michael Fuhr
On Oct 20, 2005, at 15:44 , Michael Fuhr wrote: > expression IS DISTINCT FROM expression > > For non-null inputs this is the same as the <> operator. However, > when both inputs are null it will return false, and when just one > input is null it will return true. Thus it effectively acts as > though null were a normal data value, rather than "unknown". > Interesting! Thanks, Michael. You don't happen to know off the top of your head if that's standard SQL, do you? Michael Glaesemann grzm myrealbox com
Michael Glaesemann wrote:
> if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value <>
> NEW.value) or OLD.value IS NULL or NEW.value IS NULL
>
> But that's untested and I have a hard time thinking in three-value logic.
For completeness sake; Because of lazy evaluation, that boils down to:
if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value <> NEW.value)
The last part of the expression is only evaluated if both OLD.value and
NEW.value aren't NULL.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
Alban Hertroys <alban@magproductions.nl> writes:
> Michael Glaesemann wrote:
>> if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value <>
>> NEW.value) or OLD.value IS NULL or NEW.value IS NULL
>>
>> But that's untested and I have a hard time thinking in three-value logic.
> For completeness sake; Because of lazy evaluation, that boils down to:
> if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value <> NEW.value)
> The last part of the expression is only evaluated if both OLD.value and
> NEW.value aren't NULL.
Wrong. SQL doesn't guarantee lazy evaluation. The above will work,
but it's because TRUE OR NULL is TRUE, not because anything is promised
about evaluation order.
regards, tom lane
On Thu, Oct 20, 2005 at 03:57:41PM +0900, Michael Glaesemann wrote: > On Oct 20, 2005, at 15:44 , Michael Fuhr wrote: > > expression IS DISTINCT FROM expression > > > > For non-null inputs this is the same as the <> operator. However, > > when both inputs are null it will return false, and when just one > > input is null it will return true. Thus it effectively acts as > > though null were a normal data value, rather than "unknown". > > Interesting! Thanks, Michael. You don't happen to know off the top of > your head if that's standard SQL, do you? IS DISTINCT FROM is defined in SQL:1999 and SQL:2003. -- Michael Fuhr
Tom Lane wrote:
> Wrong. SQL doesn't guarantee lazy evaluation. The above will work,
> but it's because TRUE OR NULL is TRUE, not because anything is promised
> about evaluation order.
Learned something new again, then.
I also noticed FALSE OR NULL is NULL, which went against my intuition. I
think I understand why:
- TRUE OR "unknown" can only evaluate to TRUE again; "unknown" is not
relevant for the operation.
- FALSE OR "unknown" remains "unknown", because "unknown" may be TRUE or
it may not. If it is, then the result would be TRUE, but if it isn't it
would be FALSE, but we don't know...
This 3-state logic can have some interesting results...
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
On Oct 20, 2005, at 23:45 , Michael Fuhr wrote: > On Thu, Oct 20, 2005 at 03:57:41PM +0900, Michael Glaesemann wrote: > >> On Oct 20, 2005, at 15:44 , Michael Fuhr wrote: >> >>> expression IS DISTINCT FROM expression >>> >>> For non-null inputs this is the same as the <> operator. However, >>> when both inputs are null it will return false, and when just one >>> input is null it will return true. Thus it effectively acts as >>> though null were a normal data value, rather than "unknown". >>> >> >> Interesting! Thanks, Michael. You don't happen to know off the top of >> your head if that's standard SQL, do you? >> > > IS DISTINCT FROM is defined in SQL:1999 and SQL:2003. Thanks! Michael Glaesemann grzm myrealbox com
BTW, it (the SQL spec I presume) has always seemed contradictory to me that you can't do: select * from table where field=null; but can do: update table set field=null; (as opposed to 'update table set field to null' or similar). CSN __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
CSN <cool_screen_name90001@yahoo.com> writes:
> BTW, it (the SQL spec I presume) has always seemed
> contradictory to me that you can't do:
> select * from table where field=null;
> but can do:
> update table set field=null;
This only seems contradictory if you fail to make the distinction
between "=" used as a comparison operator and "=" used to mean
assignment.
Personally I prefer programming languages that actually spell the
two concepts differently ... but enough don't that one has to learn
to live with it.
regards, tom lane
On 10/20/2005 6:10 AM, Alban Hertroys wrote: > Michael Glaesemann wrote: >> if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value <> >> NEW.value) or OLD.value IS NULL or NEW.value IS NULL >> >> But that's untested and I have a hard time thinking in three-value logic. > > For completeness sake; Because of lazy evaluation, that boils down to: > > if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value <> NEW.value) That would result in TRUE if both, OLD and NEW are NULL. Is that what you intended? Jan > > The last part of the expression is only evaluated if both OLD.value and > NEW.value aren't NULL. > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #