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: > On Aug 22, 2012, at 9:23, Michael Sacket <msac...@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! >> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
