Thread: weird results from trivial SELECT statement
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!
From my knowledge, I think that NULL is not a value, it's rather the absense of a value. It doesn't matter if you use = or !=, testing 'disabled' against a NULL column will always fail. Why dont you try a WHERE clause like WHERE active != 'disabled OR active IS NULL if you want the NULL records as well. HTH, Best, Oliveiros ----- Original Message ----- From: "Lonni J Friedman" <netllama@gmail.com> To: <pgsql-novice@postgresql.org> Sent: Wednesday, April 27, 2011 5:25 PM Subject: [NOVICE] weird results from trivial SELECT statement > 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! > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
Thanks for the reply. This corresponds with the behavior that I'm seeing. On Wed, Apr 27, 2011 at 9:47 AM, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote: > From my knowledge, I think that NULL is not a value, it's rather the absense > of a value. > It doesn't matter if you use = or !=, testing 'disabled' against a NULL > column will always fail. > > Why dont you try a WHERE clause like > WHERE active != 'disabled > OR active IS NULL > if you want the NULL records as well. > > HTH, > > > Best, > Oliveiros > > > ----- Original Message ----- From: "Lonni J Friedman" <netllama@gmail.com> > To: <pgsql-novice@postgresql.org> > Sent: Wednesday, April 27, 2011 5:25 PM > Subject: [NOVICE] weird results from trivial SELECT statement > > >> 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!
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 performcomparison operations on NULL. NULL is not comparable to anything, including itself. NULL == NULL will return FALSEon most systems. Some of the interpretations of NULL include, it is unknown if there should be data here, there is no data here, and thereshould 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.
Brent Dombrowski wrote: > 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. > 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. "Most" databases? "Most" systems? *ALL* SQL-compliant products. -- Lew Honi soit qui mal y pense. http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
(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