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

Re: weird results from trivial SELECT statement

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: weird results from trivial SELECT statement
Date: 2011-04-28 13:31:28
Message-ID: ipbq6s$jem$1@news.albasani.net (view raw or flat)
Thread:
Lists: pgsql-novice
(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

In response to

pgsql-novice by date

Next:From: LewDate: 2011-04-28 13:35:07
Subject: Re: weird results from trivial SELECT statement
Previous:From: Tom LaneDate: 2011-04-28 13:28:58
Subject: Re: failure and silence of SQL commands

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