Re: Strange behavior on plpgsql trigger function in if comparison - Mailing list pgsql-hackers

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