Postgres Pro
Downloads
Home   >   mailing lists

Thread: ...

...

From
Bhuvan A
Date:
On Mar 11, Stephan Szabo wrote:

>
> 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.

where did you get this definition of behaviour!? is it applicable only to
postgres or ..?  its quite strange yaar!
>

Re:

From
Yury Bokhoncovich
Date:
Hello!

On Mon, 11 Mar 2002, Bhuvan A wrote:

[skip]
> > 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.
>
> where did you get this definition of behaviour!? is it applicable only to
> postgres or ..?  its quite strange yaar!

it's standard SQL:
true and true gives true
true and false gives false
null and true gives null
null and false gives null

RTFM! Quick check on MySQL and MSSQL gives the same results.
AFAIK Interbase and Oracle behaves similarly.
So theory is confirmed by practice.
It is very amazing to listen about contrary thing, could you please tell
where you saw this?

--
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

Re:

From
Peter Eisentraut
Date:
Bhuvan A writes:

> > 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.
>
> where did you get this definition of behaviour!? is it applicable only to
> postgres or ..?  its quite strange yaar!

It's how the SQL standard defines it.  If other database software claims
to comply to SQL it should also behave that way.  (Before you complain
about the SQL standard, its definition was adopted from the common
mathematical notion of three-valued Boolean logic.)

If you want

   a <> b

to return true if one of a and b is NULL and the other is not NULL then
you will have to do a bit more work.  For example,

   (a <> b) or (a is null and b is not null) or (a is not null and b is null)

--
Peter Eisentraut   peter_e@gmx.net

Re:

From
Stephan Szabo
Date:
On Mon, 11 Mar 2002, Bhuvan A wrote:

> > 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.
>
> where did you get this definition of behaviour!? is it applicable only to
> postgres or ..?  its quite strange yaar!

It makes sense if you think of NULL as an unknown value.  You don't know
if this unknown value is different from any particular value (even another
NULL).  NULLs are one of the ugliest parts of SQL.

In case you're wondering for SQL92 (at least the draft I have), the
section is 8.2 <comparison predicate>, General Rules 1.

         1) Let X and Y be any two corresponding <row value constructor
            element>s. Let XV and YV be the values represented by X and Y,
            respectively.

            Case:

            a) If XV or YV is the null value, then "X <comp op> Y" is un-
              known.

Re:

From
Juliano Ignacio
Date:
On Mon, 11 Mar 2002, Bhuvan A wrote:

> 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.
>
> where did you get this definition of behaviour!?
> is it applicable only to
> postgres or ..?  its quite strange yaar!

I think that you are searching for a solution, so,
view the COALESCE SQL function in PostgreSQL
documentation. It will help you in your compares.

Juliano S. Ignacio
jsignacio@yahoo.com

__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/