Strange behavior on plpgsql trigger function in if comparison

Omar Bettin wrote:

Hi,

I have to compare an OLD.Value with a NEW.Value on PostgreSQL 8.2.4
plpgsql trigger function:

IF OLD.Value <> NEW.Value THEN
...

but, if OLD.Value IS NULL and NOT NEW.Value IS NULL the previous IF does
not work and I have to use this method:

IF (OLD.Value <> NEW.Value) OR (OLD.Value IS NULL AND NOT NEW.Value IS
NULL) THEN
...

this works!
Is that normal ?

yeah that is expected behaviour - NULL does not equal NULL. In cases
like that you might wnat to use:

IF OLD.Value IS DISTINCT FROM NEW.Value THEN

which handles NULL in the way you seem to expect it.
also look at
http://www.postgresql.org/docs/current/static/functions-comparison.html
for more details on that topic.

Stefan

Omar Bettin wrote:

Hi,

I have to compare an OLD.Value with a NEW.Value on PostgreSQL 8.2.4
plpgsql trigger function:

IF OLD.Value <> NEW.Value THEN
...

but, if OLD.Value IS NULL and NOT NEW.Value IS NULL the previous IF
does not work and I have to use this method:

IF (OLD.Value <> NEW.Value) OR (OLD.Value IS NULL AND NOT NEW.Value
IS NULL) THEN
...

this works!
Is that normal ?

Please do not ask usage questions on the -hackers list. This should have
been asked on the pgsql-general list.

cheers

andrew