Re: NULL value comparison

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