From: | Thom Brown <thombrown(at)gmail(dot)com> |
---|---|
To: | Mick <mickg01(at)verizon(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: null vs empty string |
Date: | 2010-07-01 08:31:10 |
Message-ID: | AANLkTikbelygpRYpMhNdrqKi5BaO0lcgPdKZtzVg0KW_@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 1 July 2010 04:59, Mick <mickg01(at)verizon(dot)net> wrote:
> On 06/30/2010 08:41 AM, Thom Brown wrote:
>>
>> On 30 June 2010 13:21, Kent Thomas<kent(at)solarbee(dot)com> wrote:
>>>
>>> I have the following query:
>>>
>>> SELECT * FROM "sales_projects" WHERE (((sales_projects.prospect ILIKE
>>> E'%rancho murieta%') OR (sales_projects.prospect_type ILIKE E'%rancho
>>> murieta%') OR (sales_projects.application ILIKE E'%rancho murieta%') OR
>>> (sales_projects.project ILIKE E'%rancho murieta%') OR (sales_projects.city
>>> ILIKE E'%rancho murieta%') OR (sales_projects.state ILIKE E'%rancho
>>> murieta%') OR (sales_projects.project_status ILIKE E'%rancho murieta%')) AND
>>> (((sales_projects.project_status != E'Dead') AND
>>> (sales_projects.project_status != E'Ordered')) AND ((sales_projects.status
>>> IN (E'Active',E'Expired')) AND (sales_projects.kind = E'Project'))))
>>>
>>> Yes, it is ugly, but that's not the issue. This query returns just one
>>> record when I would expect it to return two. The only difference in the two
>>> records is in the sales_projects.project_status field. One record has an
>>> empty string, the second has a null value. The NULL value in
>>> sales_projects.project_status is not returned.
>>>
>>> Can someone explain why the NULL value in sales_projects.project_status
>>> field does not fit this query?
>>> Secondly, can you offer some advice to EXCLUDE records where
>>> sales_projects.project_status is Ordered and Dead?
>>>
>>> Thanks a million for any help.
>>
>> NULL values won't be returned if you're matching against a value, or
>> excluding specific values from the result because NULL can't be
>> compared with non-nulls.
>>
>> If sale_projects.project_status has a NULL value, checking to see
>> whether it's not equal to a value won't return it because it isn't
>> known. You would have to use "OR IS NULL" in where appropriate.
>>
>> An analogy would be having 3 boxes. 1 has an orange in with the lid
>> off, 1 with an apple with the lid off, and 1 with the lid on. You
>> can't say either match the contents of the 3rd box because you don't
>> know what's in it.
>>
>> Regards
>>
>> Thom
>>
> Schroedinger's cat!
>
Although, to confuse things further, this is a great read:
http://thoughts.j-davis.com/2009/08/02/what-is-the-deal-with-nulls/
Thom
From | Date | Subject | |
---|---|---|---|
Next Message | slapo | 2010-07-01 12:29:03 | How to find out whether a row is currently referenced by a row in a different table? |
Previous Message | Xavier Robin | 2010-07-01 07:25:12 | Re: Timestamp with time zone |