Re: NULL value comparison - Mailing list pgsql-general

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
appropriateNOT 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@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
areNULL 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
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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general