Re: weird results from trivial SELECT statement

(Please do not top-post.)

Lonni J Friedman wrote:
>>> Greetings,
>>> I've got a Postgresql-8.4.x instance with a bunch of tables taht have
>>> a text column (called 'active') that can contain any one of the
>>> following values:
>>> NULL
>>> 'disabled'
>>> <some other text string>
>>>
>>> When I run the following query, it seems to ignore NULL values:
>>> SELECT * FROM mytbl WHERE active!='disabled'
>>>
>>> and only returns rows where active!='disabled' AND active IS NOT NULL.
>>> Is postgresql [sic] implicitly assuming that I want non-NULL values?

This is just one of the very basic rules of SQL, which are important to know
if you're going to use Postgres successfully. It's kind of hard to program
successfully with a computer language if you don't know the language.

The answer is in the manual, should you have an interest in RTFM.
<http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html>
"Do not write expression = NULL because NULL is not "equal to" NULL. (The null
value represents an unknown value, and it is not known whether two unknown
values are equal.) This behavior conforms to the SQL standard.

"... Ordinary comparison operators yield null (signifying "unknown"), not true
or false, when either input is null. For example, 7 = NULL yields null. ..."

For the ordinary comparison operators in SQL, { FALSE, TRUE, NULL } works like
{ FALSE, TRUE, UNKNOWN } in a 3-valued logic.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg