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

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

>
> thnx,
> peter
>
>
> --
> mag. peter pilsl
>
> phone: +43 676 3574035
> fax : +43 676 3546512
> email: pilsl(at)goldfisch(dot)at
> sms : pilsl(at)max(dot)mail(dot)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(at)postgresql(dot)org)