Re: NULL value comparison

On Aug 22, 2012, at 9:23, Michael Sacket <msacket(at)gammastream(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general