Re: Checking inequality

On Sat, Dec 11, 2004 at 02:42:21PM +0200, Vitaly Belman wrote:

> IF old.series_id<>new.series_id THEN
> ...
> The problem is that series_id can change to be NULL in which case I
> have problems as "NULL <> 7" doesn't return "true".
>
> What can I do to check inequality even in the case that old or new
> series_id is NULL? I'd prefer not to do zillion silly comparisons
> (like "IS NULL and IS NOT NULL"). Is there an easy way?

You could COALESCE the fields to a value that would normally be
invalid:

IF COALESCE(old.series_id, -1) <> COALESCE(new.series_id, -1) THEN

This assumes that you'd want two NULLs to compare as equal.

You could also use CREATE OPERATOR to create an operator that
behaves as you'd like. This has been discussed recently; see
the list archives.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/