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

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

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 whether it 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 have to 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