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

Re: null vs empty string

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: null vs empty string
Date: 2010-06-30 20:59:03
Message-ID: BLU0-SMTP4839D736FB06D0007AD6FDACCC0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice
Kent Thomas 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?  


Others have already explained this; but they did not mention coalecse() 
which might be useful for you.


> Secondly, can you offer some advice to EXCLUDE records where sales_projects.project_status is Ordered and Dead?


You already do this with

(sales_projects.project_status != E'Dead') AND 
(sales_projects.project_status != E'Ordered')

As a general rule:

"(NOT A) AND (NOT B)" is the same as "NOT (A OR B)"

So you could also write:

NOT ( (sales_projects.project_status = E'Dead') OR 
(sales_projects.project_status != E'Ordered')

If you wish to avoid some of the "ugly", then try:

sales_projects.project_status NOT IN (E'Dead',E'Ordered')

Of course, all these examples will still EXCLUDE the NULL values; so 
perhaps you want this:

coalesce(sales_projects.project_status,'') NOT IN (E'Dead',E'Ordered')

In response to

pgsql-novice by date

Next:From: BidskiDate: 2010-06-30 22:09:24
Subject: Re: Compiling under MSYS and Windows 7
Previous:From: Tim LandscheidtDate: 2010-06-30 18:29:14
Subject: Re: Timestamp with time zone

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