Checking inequality
I have the following code in one of my trigger functions
---------------------------------------------------------------
IF old.series_id<>new.series_id THEN
...
...
END IF;
---------------------------------------------------------------
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?
--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent@hotmail.com
Yahoo!: VitalyBe
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/
COALESCE is good enough for me. Thanks.
On Sat, 11 Dec 2004 09:33:32 -0700, Michael Fuhr <mike@fuhr.org> wrote:
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/
--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent@hotmail.com
Yahoo!: VitalyBe
On Sat, 11 Dec 2004, Vitaly Belman wrote:
I have the following code in one of my trigger functions
---------------------------------------------------------------
IF old.series_id<>new.series_id THEN
...
...
END IF;
---------------------------------------------------------------The problem is that series_id can change to be NULL in which case I
have problems as "NULL <> 7" doesn't return "true".
old.series_id IS DISTINCT FROM new.series_id may do what you want
depending on how you want NULLs to compare (IS DISTINCT FROM would be
false).
On Sat, Dec 11, 2004 at 08:54:40AM -0800, Stephan Szabo wrote:
old.series_id IS DISTINCT FROM new.series_id may do what you want
depending on how you want NULLs to compare (IS DISTINCT FROM would be
false).
I forgot about IS DISTINCT FROM. I like that better than using
COALESCE, as I had suggested.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
True. It does seem more proper.
Thanks.
On Sat, 11 Dec 2004 10:57:25 -0700, Michael Fuhr <mike@fuhr.org> wrote:
On Sat, Dec 11, 2004 at 08:54:40AM -0800, Stephan Szabo wrote:
old.series_id IS DISTINCT FROM new.series_id may do what you want
depending on how you want NULLs to compare (IS DISTINCT FROM would be
false).I forgot about IS DISTINCT FROM. I like that better than using
COALESCE, as I had suggested.--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent@hotmail.com
Yahoo!: VitalyBe
