Re: it's not NULL, then what is it? - Mailing list pgsql-sql

<p><font size="2">Hi Rob,<br /><br /> > Maybe something like<br /><br /> >     select ']' || maf::text || '[' --
justto see where the value<br /> >     start/stops<br /><br /> It prints many (1,132,691 to be exact) lines
consistingof 7 space<br /> characters followed by many lines like:<br />  ]0.0106383[<br />  ]0.0106383[<br />
 ]0.0106383[<br/><br /><br /> > or<br /><br /> >     select length(maf::text)<br /><br /> This results in many
linesof 7 space characters, followed by a<br /> bunch of 9's, 10's, 8's...<br /><br /> > but I suspect you're
gettingNAN or something unprintable in your<br /> > environment?<br /><br /> Yes, me too.  But,<br /><br />  
canon=#select maf<br />   canon-#   from gallo.sds_seq_reg_shw<br />   canon-#  where maf = NAN;<br />   ERROR:  column
"nan"does not exist<br />   LINE 3:  where maf = NAN;<br />                        ^<br /><br /> What can I put to the
rightof equal sign to make the query work?<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br
/><br/><br /> -----Original Message-----<br /> From: Rob Sargent [<a
href="mailto:robjsargent@gmail.com">mailto:robjsargent@gmail.com</a>]<br/> Sent: Tue 6/30/2009 3:24 PM<br /> To: Tena
Sakai<br/> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] it's not NULL, then what is it?<br /><br /> Tena Sakai
wrote:<br/> ><br /> > Hi Everybody,<br /> ><br /> > I have a table called gallo.sds_seq_reg_shw,<br /> >
whichis like:<br /> ><br /> >   canon=# \d gallo.sds_seq_reg_shw<br /> >        Table
"gallo.sds_seq_reg_shw"<br/> >         Column      |  Type   | Modifiers<br /> >  
------------------+---------+-----------<br/> >    name             | text    |<br /> >    response         |
text   |<br /> >    n                | integer |<br /> >    source           | text    |<br /> >   
test            | text    |<br /> >    ref              | text    |<br /> >    value            | real    |<br />
>   pvalue.term      | real    |<br /> >    stars.term       | text    |<br /> >    gtclass.test     | text   
|<br/> >    fclass.test      | text    |<br /> >    gtclass.ref      | text    |<br /> >    fclass.ref       |
text   |<br /> >    markerid         | integer |<br /> >    maf              | real    |<br /> >   
chromosome      | text    |<br /> >    physicalposition | integer |<br /> >    id               | text    |<br />
>   ctrast           | text    |<br /> > <br /> > I am intereseted in the column maf (which is real):<br />
><br/> >   canon=# select maf from gallo.sds_seq_reg_shw<br /> >   canon-#  order by maf asc;<br />
>       maf   <br /> >   -------------<br /> >    0.000659631<br /> >    0.000659631<br /> >   
0.000659631<br/> >    0.000659631<br /> >         .<br /> >   (trunacated for the interest of breivity)<br />
>        .<br /> ><br /> > Another way to look at this column is:<br /> ><br /> >   canon=# select maf
fromgallo.sds_seq_reg_shw<br /> >   canon-#  order by maf desc;<br /> >        maf   <br /> >  
-------------<br/> >             <br /> >             <br /> >             <br /> >         .<br /> >  
(trunacatedfor the interest of breivity)<br /> >         .<br /> ><br /> > These rows shown are blanks, as far
asI can tell.<br /> > But...<br /> ><br /> >   canon=# select count(maf) from gallo.sds_seq_reg_shw;<br />
>   count<br /> >   -------<br /> >    67284<br /> >   (1 row)<br /> > <br /> >   canon=# select
count(maf)from gallo.sds_seq_reg_shw<br /> >   canon-#  where maf ISNULL;<br /> >    count<br /> >  
-------<br/> >        0<br /> >   (1 row)<br /> > <br /> >   canon=#<br /> >   canon=# select count(maf)
fromgallo.sds_seq_reg_shw<br /> >   canon-#  where maf NOTNULL;<br /> >    count<br /> >   -------<br />
>   67284<br /> >   (1 row)<br /> ><br /> > My confusion is that if they are real and not null,<br /> >
whatare they?  How would I construct a query to do<br /> > something like:<br /> ><br /> >  select
count(maf)<br/> >    from gallo.sds_seq_reg_shw<br /> >   where maf ISBLANK;<br /> ><br /> > Thank you in
advance.<br/> ><br /> > Regards,<br /> ><br /> > Tena Sakai<br /> > tsakai@gallo.ucsf.edu<br /> ><br
/>Maybe something like<br /><br />     select ']' || maf::text || '[' -- just to see where the value<br />    
start/stops<br/><br /> or<br /><br />     select length(maf::text)<br /><br /> but I suspect you're getting NAN or
somethingunprintable in your<br /> environment?<br /><br /></font>