NULL value comparison
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
Thanks!
Michael
Example:
CREATE TABLE users (
"name" char(50) NOT NULL,
"is_enabled" char
)
insert into users (name, is_enabled) values ('Michael', 'Y');
insert into users (name, is_enabled) values ('Jeremy', 'N');
insert into users (name, is_enabled) values ('Sherry', NULL);
select * from users where is_enabled<>'Y';
+----------------------------------------------------+------------+
| name | is_enabled |
+----------------------------------------------------+------------+
| Jeremy | N |
+----------------------------------------------------+------------+
1 rows in set (0.03 sec)
2012/8/22 Michael Sacket <msacket@gammastream.com>:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
no - NULL is not comparable with any value
your query should be
WHERE is_enabled <> 'Y' or is_enabled IS NULL
or
WHERE is_enabled IS DISTINCT FROM 'Y'
Regards
Pavel Stehule
Show quoted text
Thanks!
MichaelExample:
CREATE TABLE users (
"name" char(50) NOT NULL,
"is_enabled" char
)insert into users (name, is_enabled) values ('Michael', 'Y');
insert into users (name, is_enabled) values ('Jeremy', 'N');
insert into users (name, is_enabled) values ('Sherry', NULL);select * from users where is_enabled<>'Y'; +----------------------------------------------------+------------+ | name | is_enabled | +----------------------------------------------------+------------+ | Jeremy | N | +----------------------------------------------------+------------+ 1 rows in set (0.03 sec)--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 08/22/2012 06:23 AM, Michael Sacket wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
See below for explanation:
http://www.postgresql.org/docs/9.1/interactive/functions-comparison.html
Thanks!
Michael
--
Adrian Klaver
adrian.klaver@gmail.com
On Aug 22, 2012, at 9:23, Michael Sacket <msacket@gammastream.com> wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
The only record known to be not equal to "Y" is "N" since it is possible the unknown value represented by NULL could be "Y". If you really want both you need to use IS DISTINCT FROM
http://www.postgresql.org/docs/9.1/static/functions-comparison.html
Note a useful alternative is
COALESCE(is_enabled, 'N') <> 'Y'
This explicitly indicates that unknown values are to be treated as 'N'
A better solution is not allow NULL values in the first place. Add a NOT NULL constraint on the column and a DEFAULT expression on the table as well.
You should consider enums and/or a check constraint for allowed values as well.
Show quoted text
Thanks!
MichaelExample:
CREATE TABLE users (
"name" char(50) NOT NULL,
"is_enabled" char
)insert into users (name, is_enabled) values ('Michael', 'Y');
insert into users (name, is_enabled) values ('Jeremy', 'N');
insert into users (name, is_enabled) values ('Sherry', NULL);select * from users where is_enabled<>'Y'; +----------------------------------------------------+------------+ | name | is_enabled | +----------------------------------------------------+------------+ | Jeremy | N | +----------------------------------------------------+------------+ 1 rows in set (0.03 sec)--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Michael.
NULL is not any specific value. Thus Pg correctly doesnot tell you that it
is <>'Y'. It is NULL means that we dont know the value. Thus it may be 'Y'
as much as it may not be 'Y'. The comparison is not applicable in the case
of NULL and that's why there are the IS NULL and IS NOT NULL operators.
Regards,
Thalis
On Aug 22, 2012 10:24 AM, "Michael Sacket" <msacket@gammastream.com> wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd
expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL.
Perhaps my expectations are misguided. Any thoughts would be appreciated.
Show quoted text
Thanks!
MichaelExample:
CREATE TABLE users (
"name" char(50) NOT NULL,
"is_enabled" char
)insert into users (name, is_enabled) values ('Michael', 'Y');
insert into users (name, is_enabled) values ('Jeremy', 'N');
insert into users (name, is_enabled) values ('Sherry', NULL);select * from users where is_enabled<>'Y'; +----------------------------------------------------+------------+ | name | is_enabled | +----------------------------------------------------+------------+ | Jeremy | N | +----------------------------------------------------+------------+ 1 rows in set (0.03 sec)--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: CAEkCx9FX23HuFeSbh=+9Obw5ACsaPYQHxk7pBRq6YYC7ABTkew@mail.gmail.com
Thank you all very much!
Unfortunately I can't change the query... but I can modify the data. I updated the NULL values to 'N' and put the appropriate NOT NULL constraint and a default value of 'N'.
On Aug 22, 2012, at 8:37 AM, David Johnston wrote:
Show quoted text
On Aug 22, 2012, at 9:23, Michael Sacket <msacket@gammastream.com> wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
The only record known to be not equal to "Y" is "N" since it is possible the unknown value represented by NULL could be "Y". If you really want both you need to use IS DISTINCT FROM
http://www.postgresql.org/docs/9.1/static/functions-comparison.html
Note a useful alternative is
COALESCE(is_enabled, 'N') <> 'Y'
This explicitly indicates that unknown values are to be treated as 'N'
A better solution is not allow NULL values in the first place. Add a NOT NULL constraint on the column and a DEFAULT expression on the table as well.
You should consider enums and/or a check constraint for allowed values as well.
Thanks!
MichaelExample:
CREATE TABLE users (
"name" char(50) NOT NULL,
"is_enabled" char
)insert into users (name, is_enabled) values ('Michael', 'Y');
insert into users (name, is_enabled) values ('Jeremy', 'N');
insert into users (name, is_enabled) values ('Sherry', NULL);select * from users where is_enabled<>'Y'; +----------------------------------------------------+------------+ | name | is_enabled | +----------------------------------------------------+------------+ | Jeremy | N | +----------------------------------------------------+------------+ 1 rows in set (0.03 sec)--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 08/22/2012 09:37 PM, David Johnston wrote:
On Aug 22, 2012, at 9:23, Michael Sacket <msacket@gammastream.com> wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
The only record known to be not equal to "Y" is "N" since it is possible the unknown value represented by NULL could be "Y". If you really want both you need to use IS DISTINCT FROM
http://sqlblog.com/blogs/paul_nielsen/archive/2007/11/11/the-real-problem-with-null.aspx
Teaching that NULL means "unknown" tends to lead to confusion down the
track, in cases where NULL means "no value" or "bork bork oogabooga"
instead.
Null is interpreted as "the known value 'no value'" by aggregate
functions; were that not the case, the result of:
regress=# SELECT SUM(i) FROM ( VALUES (1),(2),(NULL),(3) ) x(i);
sum
-----
6
(1 row)
would be NULL, not 6, and the result of:
regress=# SELECT SUM(i) FROM generate_series(1,0) i;
sum
-----
(1 row)
ie a sum on no values would not make sense; it's "no value" here not
"unknown".
Null isn't consistent in meaning, and trying to treat it as "unknown"
just leads to confusion. It'd be nice if SQL had separate "UNKNOWN" and
"NO_VALUE_OR_NA" keywords instead of "NULL", but alas, it doesn't - and
I'm not sure that'd cover all the cases either.
--
Craig Ringer
On 08/22/2012 10:58 PM, Michael Sacket wrote:
Thank you all very much!
Unfortunately I can't change the query... but I can modify the data. I updated the NULL values to 'N' and put the appropriate NOT NULL constraint and a default value of 'N'.
What tool/app is generating the query? They need to be told they're
doing something wrong and unsafe - unless it's documented that the
target column must be NOT NULL, anyway.
PostgreSQL has a workaround for one such wrong, broken and unsafe
program, Microsoft Access. However the workaround is limited to
transforming "= NULL" to "IS NOT NULL"; it doesn't actually change the
semantics of NULL.
--
Craig Ringer
On Aug 22, 2012, at 8:17 PM, Craig Ringer wrote:
On 08/22/2012 10:58 PM, Michael Sacket wrote:
Thank you all very much!
Unfortunately I can't change the query... but I can modify the data. I updated the NULL values to 'N' and put the appropriate NOT NULL constraint and a default value of 'N'.
What tool/app is generating the query? They need to be told they're doing something wrong and unsafe - unless it's documented that the target column must be NOT NULL, anyway.
PostgreSQL has a workaround for one such wrong, broken and unsafe program, Microsoft Access. However the workaround is limited to transforming "= NULL" to "IS NOT NULL"; it doesn't actually change the semantics of NULL.
Well... the query would be my fault, before I learned the value of having most columns NOT NULL. It's from an old WebObjects application. EOF at the time was generally not especially helpful with modeling boolean values and hooking them up to checkboxes so I (erroneously as it turns out) defined true to be 'Y' and anything else as false. In any case, it worked without issue until I switched to PostgreSQL yesterday and I didn't understand why. I was looking for the quickest route to a working version without having to coax EOF into generating the a different select.
The good news is I now have the proper constraints in place and the app and it's 130 tables are working with PostgreSQL in less than a day. Additionally, thanks to this list, I believe I understand the semantics of NULL now.
Thanks!
Michael
On 08/23/2012 10:32 AM, Michael Sacket wrote:
The good news is I now have the proper constraints in place and the app and it's 130 tables are working with PostgreSQL in less than a day.
Wow, that's cool, especially without SQL changes.
What was the previous database? I'm curious now.
--
Craig Ringer
On Aug 22, 2012, at 9:38 PM, Craig Ringer wrote:
On 08/23/2012 10:32 AM, Michael Sacket wrote:
The good news is I now have the proper constraints in place and the app and it's 130 tables are working with PostgreSQL in less than a day.
Wow, that's cool, especially without SQL changes.
What was the previous database? I'm curious now.
The database was OpenBase. I did an ascii backup and used some scripts I created last summer to make some small changes (mostly changing types on the create statements)... then ran it through psql. On the WebObjects side I just had to update the external types in the EOModel to match the PostgreSQL ones. Turned out to be a fairly straightforward process.
In any case, we've been using PostgreSQL for all new development for about 1.5 years and have found it to be quite reliable, quick, and elegant. OpenBase was quick and reliable for us as well; however, due to its limited adoption outside of MacOSX and WebObjects we were always having to write our own tools and frameworks to work with it.