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)

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 #