Thread: select, where and null-values (or: select null<>'1' is fuzzy)

I've a problem when selecting values out a table.

manana=# select * from test;
 l1 | l2 | l3
----+----+----
 1  | 2  | 3
 2  | 3  | 4
 3  | 4  | 5
 4  | 5  | 6
    | 5  | 6
(5 rows)

where l1 is NULL in the last line.
now I do

manana=# select * from test where l1<>1;
 l1 | l2 | l3
----+----+----
 2  | 3  | 4
 3  | 4  | 5
 4  | 5  | 6
(3 rows)
and dont get the line with the null-entry !
In fact the problem is also describeable like this:

manana=# select NULL<>'1';
 ?column?
----------

(1 row)

This gives neither true nor false.

Why is this like this and how can I work around ?

thnx,
peter


--
mag. peter pilsl

phone: +43 676 3574035
fax  : +43 676 3546512
email: pilsl@goldfisch.at
sms  : pilsl@max.mail.at

pgp-key available

Peter Pilsl writes:

> manana=# select NULL<>'1';
>  ?column?
> ----------
>
> (1 row)
>
> This gives neither true nor false.

It gives NULL. This is correct three-valued Boolean logic.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

On Tue, 12 Jun 2001, Peter Pilsl wrote:

> I've a problem when selecting values out a table.
>
> manana=# select * from test;
>  l1 | l2 | l3
> ----+----+----
>  1  | 2  | 3
>  2  | 3  | 4
>  3  | 4  | 5
>  4  | 5  | 6
>     | 5  | 6
> (5 rows)
>
> where l1 is NULL in the last line.
> now I do
>
> manana=# select * from test where l1<>1;
>  l1 | l2 | l3
> ----+----+----
>  2  | 3  | 4
>  3  | 4  | 5
>  4  | 5  | 6
> (3 rows)
> and dont get the line with the null-entry !
> In fact the problem is also describeable like this:
>
> manana=# select NULL<>'1';
>  ?column?
> ----------
>
> (1 row)
>
> This gives neither true nor false.
>
> Why is this like this and how can I work around ?

Don't think of NULL as a value. Think of it as "unknown". Therefore since it is "unknown", Pg cannot answer to you
whetherit is = or <> to a value such as '1'. NULL infact is so damn NULL it's not even equal to itself. This is why you
haveto use the special IS [NOT] NULL operator. To get the desired last row in the result set of your query, you need to
do:

select * from test where l1<>1 OR l1 IS NULL;

but think again if you really want this additional row.


cheers,
thalis


>
> thnx,
> peter
>
>
> --
> mag. peter pilsl
>
> phone: +43 676 3574035
> fax  : +43 676 3546512
> email: pilsl@goldfisch.at
> sms  : pilsl@max.mail.at
>
> pgp-key available
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> I've a problem when selecting values out a table.

It's correct.  NULL<>1 is defined as NULL by the spec.
Where clauses only return rows that the expression is true
and so will not return the NULL row.

If you want the null rows to show up, you'll need to either
use coalesce or add or blah IS NULL to the expression.

Peter Pilsl <pilsl@goldfisch.at> writes:

> I've a problem when selecting values out a table.
>
> manana=# select * from test;
>  l1 | l2 | l3
> ----+----+----
>  1  | 2  | 3
>  2  | 3  | 4
>  3  | 4  | 5
>  4  | 5  | 6
>     | 5  | 6
> (5 rows)
>
> where l1 is NULL in the last line.
> now I do
>
> manana=# select * from test where l1<>1;
>  l1 | l2 | l3
> ----+----+----
>  2  | 3  | 4
>  3  | 4  | 5
>  4  | 5  | 6
> (3 rows)
> and dont get the line with the null-entry !

This has been discussed a lot recently.  Basically NULL means
"unknown"; so you can't say it's equal or unequal to any non-NULL
value.  The above behavior is in accordance with the SQL standard.

If you tell us what you are trying to do (rather than giving us a toy
example) perhaps we can make some suggestions.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan