From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Michael Sacket <msacket(at)gammastream(dot)com> |
Cc: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: NULL value comparison |
Date: | 2012-08-22 13:37:08 |
Message-ID: | 15C3C568-CFE6-408C-A042-EBB6547D54F9@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thalis Kalfigkopoulos | 2012-08-22 13:43:13 | Re: NULL value comparison |
Previous Message | Adrian Klaver | 2012-08-22 13:30:46 | Re: NULL value comparison |