Re: 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