Thread: 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).
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
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/
Vitaly Belman <vitalyb@gmail.com> writes:
> What can I do to check inequality even in the case that old or new
> series_id is NULL?
Try IS DISTINCT FROM.
regards, tom lane