Skip site navigation (1) Skip section navigation (2)

Re: null vs empty string

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Kent Thomas <kent(at)solarbee(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: null vs empty string
Date: 2010-06-30 12:41:45
Message-ID: AANLkTikMGJg9Zx97ZD0LEhpCM65y-gEheBXm2ge9FpXA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Xavier RobinDate: 2010-06-30 12:59:37
Subject: Timestamp with time zone
Previous:From: Rob RichardsonDate: 2010-06-30 12:40:22
Subject: Re: null vs empty string

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group