Skip site navigation (1) Skip section navigation (2)

Re: weird results from trivial SELECT statement

From: Brent Dombrowski <brent(dot)dombrowski(at)gmail(dot)com>
To: Lonni J Friedman <netllama(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: weird results from trivial SELECT statement
Date: 2011-04-27 18:10:09
Message-ID: 19342B9D-1489-4E07-B207-9A40F5A2295A@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.




In response to

Responses

pgsql-novice by date

Next:From: John PayneDate: 2011-04-28 03:03:02
Subject: failure and silence of SQL commands
Previous:From: Lonni J FriedmanDate: 2011-04-27 16:52:13
Subject: Re: weird results from trivial SELECT statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group