Re: NULL value comparison

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 <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
>
>
>
> --
> 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