Thread: 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
NULLvalues:
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
thoughtswould 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
NULLvalues:
>
> 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
thoughtswould 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
>
> 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)
>
>
>
>
>
> --
> 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 NULLvalues: > > 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 thoughtswould 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 NULLvalues: > > 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 thoughtswould 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 expressionon the table as well. You should consider enums and/or a check constraint for allowed values as well. > > 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) > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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 appropriateNOT NULL constraint and a default value of 'N'. On Aug 22, 2012, at 8:37 AM, 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 areNULL 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 thoughtswould 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 expressionon the table as well. > > You should consider enums and/or a check constraint for allowed values as well. > >> >> 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) >> >> >> >> >> >> -- >> 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
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.
>
> 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)
>
>
>
>
>
> --
> 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 areNULL 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 thoughtswould 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 appropriateNOT 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. http://www.postgresql.org/docs/9.1/interactive/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS -- 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 appropriateNOT 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 documentedthat 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 islimited to transforming "= NULL" to "IS NOT NULL"; it doesn't actually change the semantics of NULL. > > http://www.postgresql.org/docs/9.1/interactive/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS Well... the query would be my fault, before I learned the value of having most columns NOT NULL. It's from an old WebObjectsapplication. EOF at the time was generally not especially helpful with modeling boolean values and hooking themup 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 thequickest 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 inless 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 PostgreSQLin 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 PostgreSQLin 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 toupdate 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 outsideof MacOSX and WebObjects we were always having to write our own tools and frameworks to work with it.