Thread: comparing null value in plpgsql.
hi,
here i have a problem in comparing null values in plpgsql. this exist
in 7.1.x and 7.2 as well.
the condition <null value> != <valid value> fails in plpgsql.
consider this function is triggered on every updation on a table.
create function ftest()
returns opaque as 'declare
begin
if new.comp_code != old.comp_code then
...
end if;
return new;
end;'
language 'plpgsql';
this condition fails if old.comp_code is null and new.comp_code has
some value.
to overcome this, i am practicing..
create function ftest()
returns opaque as 'declare
begin
if new.comp_code is not null and
old.comp_code is null then
...
else if new.comp_code != old.comp_code then
...
end if;
end if;
return new;
end;'
language 'plpgsql';
is it really a bug or i am wrong? let me know. if its a bug, when can we
expect the fix?
kindly apologize if it has been already discussed.
Regards,
Bhuvaneswaran.
On Mon, 11 Mar 2002, Bhuvan A wrote: > here i have a problem in comparing null values in plpgsql. this exist > in 7.1.x and 7.2 as well. > > the condition <null value> != <valid value> fails in plpgsql. > consider this function is triggered on every updation on a table. > > create function ftest() > returns opaque as 'declare > > begin > > if new.comp_code != old.comp_code then > ... > end if; > return new; > end;' > language 'plpgsql'; > > this condition fails if old.comp_code is null and new.comp_code has > some value. <nullvalue> != <anything> is not true, it's unknown, so the if shouldn't fire. It's a side effect of how the spec defines operations on nulls.
On Mar 10, Stephan Szabo wrote: > On Mon, 11 Mar 2002, Bhuvan A wrote: > > > here i have a problem in comparing null values in plpgsql. this exist > > in 7.1.x and 7.2 as well. > > > > the condition <null value> != <valid value> fails in plpgsql. > > consider this function is triggered on every updation on a table. > > > > create function ftest() > > returns opaque as 'declare > > > > begin > > > > if new.comp_code != old.comp_code then > > ... > > end if; > > return new; > > end;' > > language 'plpgsql'; > > > > this condition fails if old.comp_code is null and new.comp_code has > > some value. > > <nullvalue> != <anything> is not true, it's unknown, so the if shouldn't what do you mean by <nullvalue> != <anything>? > fire. It's a side effect of how the spec defines operations on nulls. > here i mean.. if <nullvalue> != <not null value>, the loop is not fired, but else loop is getting fired. why? Regards, Bhuvaneswaran.
On Mon, 11 Mar 2002, Bhuvan A wrote: > > > On Mar 10, Stephan Szabo wrote: > > > On Mon, 11 Mar 2002, Bhuvan A wrote: > > > > > here i have a problem in comparing null values in plpgsql. this exist > > > in 7.1.x and 7.2 as well. > > > > > > the condition <null value> != <valid value> fails in plpgsql. > > > consider this function is triggered on every updation on a table. > > > > > > create function ftest() > > > returns opaque as 'declare > > > > > > begin > > > > > > if new.comp_code != old.comp_code then > > > ... > > > end if; > > > return new; > > > end;' > > > language 'plpgsql'; > > > > > > this condition fails if old.comp_code is null and new.comp_code has > > > some value. > > > > <nullvalue> != <anything> is not true, it's unknown, so the if shouldn't > > what do you mean by <nullvalue> != <anything>? If you compare a NULL with anything you don't get a true value whether you're comparing with =, !=, <, >, etc... That's how it's defined to behave.
On Mar 10, Stephan Szabo wrote: > On Mon, 11 Mar 2002, Bhuvan A wrote: > > > here i have a problem in comparing null values in plpgsql. this exist > > in 7.1.x and 7.2 as well. > > > > the condition <null value> != <valid value> fails in plpgsql. > > consider this function is triggered on every updation on a table. > > > > create function ftest() > > returns opaque as 'declare > > > > begin > > > > if new.comp_code != old.comp_code then > > ... > > end if; > > return new; > > end;' > > language 'plpgsql'; > > > > this condition fails if old.comp_code is null and new.comp_code has > > some value. > > <nullvalue> != <anything> is not true, it's unknown, so the if shouldn't what do you mean by <nullvalue> != <anything>? > fire. It's a side effect of how the spec defines operations on nulls. > here i mean.. if <nullvalue> != <not null value>, the loop is not fired. why?
SQL uses tri-state logic. If any value in an expression is unknown, the
answer is unknown. The only time you can get a true or false result
from an expression is if all values are known. In SQL, null represents
an unknown value - it does not represent an empty value (such as a zero
length string). To catch all possible results of a SQL boolean
expression, you would need to do something like the following even
though it may look illogical:
case <expression>
when true then ...
when false then ...
else ...
end
or in PL/PGSQL:
if <expression> then
... -- true case
else
if !<expression> then
... -- false case
else
... -- unknown case
end if;
end if;
In your expression, 'if new.comp_code !=3D old.comp_code', here are the
possibilities:
-- Both new.comp_code and old.comp_code have values. The result of the
expression is true if these values are unequal and false if they are
equal.
-- Either new.comp_code or old.comp_code or both are a null. The result
of the expression is unknown since at least one element in the
expression is unknown.
To handle situations where an element in an expression may be null, you
need to explicitly check for nulls using 'is [not] null'. You may be
able to use PostgreSQL's coalesce function to provide a default value to
use in place of a null.
If you want your code to execute when a field changes and your
definition of change includes storing a value into a field that was null
or storing a null into a field that had a value, you need to express all
of that in your condition.
if (new.comp_code !=3D old.comp_code) or
(new.comp_code is null and old.comp_code is not null) or
(new.comp_code is not null and old.comp_code is null) then
...
end if;
This is all standard SQL.
-----Original Message-----
From: bhuvanbk@yahoo.com [mailto:bhuvanbk@yahoo.com]
Sent: Monday, March 11, 2002 2:03 AM
To: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] comparing null value in plpgsql.
On Mar 10, Stephan Szabo wrote:
> On Mon, 11 Mar 2002, Bhuvan A wrote:
>=20
> > here i have a problem in comparing null values in plpgsql. this
exist
> > in 7.1.x and 7.2 as well.
> >
> > the condition <null value> !=3D <valid value> fails in plpgsql.
> > consider this function is triggered on every updation on a table.
> >
> > create function ftest()
> > returns opaque as 'declare
> >
> > begin
> >
> > if new.comp_code !=3D old.comp_code then
> > ...
> > end if;
> > return new;
> > end;'
> > language 'plpgsql';
> >
> > this condition fails if old.comp_code is null and new.comp_code has
> > some value.
>=20
> <nullvalue> !=3D <anything> is not true, it's unknown, so the if
shouldn't
what do you mean by <nullvalue> !=3D <anything>?
> fire. It's a side effect of how the spec defines operations on nulls.
>=20
here i mean..
if <nullvalue> !=3D <not null value>, the loop is not fired. why?
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)