Re: NULL != text ?

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)