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)