DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=EnyIbCnfNAsYL7rdi7evUziWUjUgn8HIfz+NDuPAZPjnoupEOUkRfKOfA/UzHW9+rP
	H3NA9ksv+bTnSzfRSIV5a3MK2m6RkWbuTh3/07uwPZaYCba0yCkubrM2G2op9QrHvGs9
	QEshLAVnES2k6DItgqBEd/MtvVDMxQFGMoceQ=
Message-ID: <4A4A908B.1080607@gmail.com>
Date: Tue, 30 Jun 2009 16:24:11 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
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

Tena Sakai wrote:
>
> Hi Everybody,
>
> I have a table called gallo.sds_seq_reg_shw,
> which is like:
>
>   canon=# \d gallo.sds_seq_reg_shw
>        Table "gallo.sds_seq_reg_shw"
>         Column      |  Type   | Modifiers
>   ------------------+---------+-----------
>    name             | text    |
>    response         | text    |
>    n                | integer |
>    source           | text    |
>    test             | text    |
>    ref              | text    |
>    value            | real    |
>    pvalue.term      | real    |
>    stars.term       | text    |
>    gtclass.test     | text    |
>    fclass.test      | text    |
>    gtclass.ref      | text    |
>    fclass.ref       | text    |
>    markerid         | integer |
>    maf              | real    |
>    chromosome       | text    |
>    physicalposition | integer |
>    id               | text    |
>    ctrast           | text    |
>  
> I am intereseted in the column maf (which is real):
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf asc;
>        maf    
>   -------------
>    0.000659631
>    0.000659631
>    0.000659631
>    0.000659631
>         .
>   (trunacated for the interest of breivity)
>         .
>
> Another way to look at this column is:
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf desc;
>        maf    
>   -------------
>              
>              
>              
>         .
>   (trunacated for the interest of breivity)
>         .
>
> These rows shown are blanks, as far as I can tell.
> But...
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw;
>    count
>   -------
>    67284
>   (1 row)
>  
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>    count
>   -------
>        0
>   (1 row)
>  
>   canon=#
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf NOTNULL;
>    count
>   -------
>    67284
>   (1 row)
>
> My confusion is that if they are real and not null,
> what are they?  How would I construct a query to do
> something like:
>
>  select count(maf)
>    from gallo.sds_seq_reg_shw
>   where maf ISBLANK;
>
> Thank you in advance.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
Maybe something like

    select ']' || maf::text || '[' -- just to see where the value
    start/stops

or

    select length(maf::text)

but I suspect you're getting NAN or something unprintable in your 
environment?