Message-ID: <4A4A941C.2060604@pinpointresearch.com>
Date: Tue, 30 Jun 2009 15:39:24 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090318)
MIME-Version: 1.0
To: Tena Sakai <tsakai@gallo.ucsf.edu>
CC: pgsql-sql@postgresql.org
Subject: Re: it's not NULL, then what is it?
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

...
>
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>    count
>   -------
>        0
>   (1 row)
>
I believe count will only count not-null anyway so this will always 
return zero. Try count(*) instead of count(maf). Here's an example:

steve@[local]=> select * from barr;
LOG:  duration: 0.226 ms
 a | b
---+---
 a | b
 c | d
   | e
(3 rows)

steve@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is 
null') from barr;
LOG:  duration: 0.283 ms
 coalesce  | coalesce
-----------+----------
 a         | b
 c         | d
 a is null | e
(3 rows)

steve@[local]=> select count(a) from barr;
LOG:  duration: 0.339 ms
 count
-------
     2
(1 row)

steve@[local]=> select count(*) from barr where a isnull;
LOG:  duration: 0.350 ms
 count
-------
     1
(1 row)