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