Re: weird results from trivial SELECT statement

On Apr 27, 2011, at 9:25 AM, 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 implicitly assuming that I want non-NULL values?
>
> I can provide additional information, if requested.
>
> thanks!

Unfortunately, there is only one NULL and it can take on several meanings. Because of this, most databases will not perform comparison operations on NULL. NULL is not comparable to anything, including itself. NULL == NULL will return FALSE on most systems.

Some of the interpretations of NULL include, it is unknown if there should be data here, there is no data here, and there should be data here but we don't know what it is. This generates enough controversy to merit a few PhD theses. Yes, it is the database world it is possible to write a PhD thesis on nothing.

Brent D.