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