From tsakai@gallo.ucsf.edu Tue Jun  30 22:02:54 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id EEECE634679
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:02:54 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 15339-09 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:02:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id 7D7BA632307
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 19:02:47 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9F9CE.7F40EDDC"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: it's not NULL, then what is it?
Date: Tue, 30 Jun 2009 15:02:45 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: it's not NULL, then what is it?
Thread-Index: Acn5zn9AixxL9PKuRYCgQ1BEOCyqSw==
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.177 tagged_above=0 required=5 tests=AWL=0.176,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/127
X-Sequence-Number: 32732

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9F9CE.7F40EDDC
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Everybody,

I have a table called gallo.sds_seq_reg_shw,
which is like:

  canon=3D# \d gallo.sds_seq_reg_shw
       Table "gallo.sds_seq_reg_shw"
        Column      |  Type   | Modifiers=20
  ------------------+---------+-----------
   name             | text    |=20
   response         | text    |=20
   n                | integer |=20
   source           | text    |=20
   test             | text    |=20
   ref              | text    |=20
   value            | real    |=20
   pvalue.term      | real    |=20
   stars.term       | text    |=20
   gtclass.test     | text    |=20
   fclass.test      | text    |=20
   gtclass.ref      | text    |=20
   fclass.ref       | text    |=20
   markerid         | integer |=20
   maf              | real    |=20
   chromosome       | text    |=20
   physicalposition | integer |=20
   id               | text    |=20
   ctrast           | text    |=20
 =20
I am intereseted in the column maf (which is real):

  canon=3D# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf asc;
       maf    =20
  -------------
   0.000659631
   0.000659631
   0.000659631
   0.000659631
        .
  (trunacated for the interest of breivity)
        .

Another way to look at this column is:

  canon=3D# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf desc;
       maf    =20
  -------------
             =20
             =20
             =20
        .
  (trunacated for the interest of breivity)
        .

These rows shown are blanks, as far as I can tell.
But...

  canon=3D# select count(maf) from gallo.sds_seq_reg_shw;
   count=20
  -------
   67284
  (1 row)
 =20
  canon=3D# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf ISNULL;
   count=20
  -------
       0
  (1 row)
 =20
  canon=3D#=20
  canon=3D# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf NOTNULL;
   count=20
  -------
   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

------_=_NextPart_001_01C9F9CE.7F40EDDC
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>it's not NULL, then what is it?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

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

</BODY>
</HTML>
------_=_NextPart_001_01C9F9CE.7F40EDDC--



From erouse@comsquared.com Tue Jun  30 22:32:48 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6D9F763423D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:32:48 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 03647-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:32:46 -0300 (ADT)
X-Greylist: delayed 00:10:25.295282 by SQLgrey-1.7.6
Received: from smtp.comsquared.com (smtp.comsquared.com [209.155.237.140])
	by mail.postgresql.org (Postfix) with ESMTP id 0E090632BBF
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 19:32:46 -0300 (ADT)
Received: from mdpserv.comsquared.com (mdpserv [10.82.40.1])
	by smtp.comsquared.com (Switch-3.2.7/Switch-3.2.7) with ESMTP id
	n5UMMDkm012803
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 18:22:18 -0400 (EDT)
Received: from erouse052008 (erouse052008.ntc2s.comsquared.com [10.82.60.61])
	by mdpserv.comsquared.com (8.13.6+Sun/8.13.6) with ESMTP id
	n5UMMCEM003849
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 18:22:13 -0400 (EDT)
From: "Edward W. Rouse" <erouse@comsquared.com>
To: <pgsql-sql@postgresql.org>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
Subject: Re: it's not NULL, then what is it?
Date: Tue, 30 Jun 2009 18:22:12 -0400
Message-ID: <0ab801c9f9d1$376ad880$a6408980$@com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0AB9_01C9F9AF.B0593880"
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: Acn5zn9AixxL9PKuRYCgQ1BEOCyqSwAAqH3Q
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/129
X-Sequence-Number: 32734

This is a multipart message in MIME format.

------=_NextPart_000_0AB9_01C9F9AF.B0593880
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Just out of curiosity did you try maf = 0?

 

Edward W. Rouse

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tena Sakai
Sent: Tuesday, June 30, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] it's not NULL, then what is it?

 

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 


------=_NextPart_000_0AB9_01C9F9AF.B0593880
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" =
xmlns:a=3D"urn:schemas-microsoft-com:office:access" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" =
xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" =
xmlns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:z=3D"#RowsetSchema" =
xmlns:b=3D"urn:schemas-microsoft-com:office:publisher" =
xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet" =
xmlns:c=3D"urn:schemas-microsoft-com:office:component:spreadsheet" =
xmlns:odc=3D"urn:schemas-microsoft-com:office:odc" =
xmlns:oa=3D"urn:schemas-microsoft-com:office:activation" =
xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" =
xmlns:rtc=3D"http://microsoft.com/officenet/conferencing" =
xmlns:D=3D"DAV:" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" =
xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/meetings/" =
xmlns:x2=3D"http://schemas.microsoft.com/office/excel/2003/xml" =
xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" =
xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" =
xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/directory/" =
xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" =
xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint/dsp" =
xmlns:udc=3D"http://schemas.microsoft.com/data/udc" =
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema" =
xmlns:sub=3D"http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/"=
 xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" =
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" =
xmlns:sps=3D"http://schemas.microsoft.com/sharepoint/soap/" =
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =
xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" =
xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" =
xmlns:udcp2p=3D"http://schemas.microsoft.com/data/udc/parttopart" =
xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/workflow/" =
xmlns:dsss=3D"http://schemas.microsoft.com/office/2006/digsig-setup" =
xmlns:dssi=3D"http://schemas.microsoft.com/office/2006/digsig" =
xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/2006/digital-sig=
nature" =
xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compatibility/2006=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns:mrels=3D"http://schemas.openxmlformats.org/package/2006/relationshi=
ps" xmlns:spwp=3D"http://microsoft.com/sharepoint/webpartpages" =
xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/services/2006/types"=
 =
xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/services/2006/messag=
es" =
xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/=
" =
xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPortalServer/Pub=
lishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" =
xmlns:st=3D"&#1;" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<title>it's not NULL, then what is it?</title>
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p
	{mso-style-priority:99;
	mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman","serif";}
span.EmailStyle18
	{mso-style-type:personal-reply;
	font-family:"Calibri","sans-serif";
	color:#1F497D;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Just out of curiosity did you try maf =3D =
0?<o:p></o:p></span></p>

<p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p>&nbsp;</o:p></span></p>

<div>

<p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Edward W. Rouse<o:p></o:p></span></p>

</div>

<p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p>&nbsp;</o:p></span></p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt =
0in 0in 0in'>

<p class=3DMsoNormal><b><span =
style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span>=
</b><span
style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"'> =
pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] <b>On Behalf Of </b>Tena =
Sakai<br>
<b>Sent:</b> Tuesday, June 30, 2009 6:03 PM<br>
<b>To:</b> pgsql-sql@postgresql.org<br>
<b>Subject:</b> [SQL] it's not NULL, then what is =
it?<o:p></o:p></span></p>

</div>

</div>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

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

</div>

</body>

</html>

------=_NextPart_000_0AB9_01C9F9AF.B0593880--




From robjsargent@gmail.com Tue Jun  30 22:24:26 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 84A576333ED
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:24:26 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 01937-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:24:23 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.173])
	by mail.postgresql.org (Postfix) with ESMTP id 9A033632BBF
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 19:24:23 -0300 (ADT)
Received: by wf-out-1314.google.com with SMTP id 28so180225wfc.28
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 15:24:22 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from
	:user-agent:mime-version:to:cc:subject:references:in-reply-to
	:content-type:content-transfer-encoding;
	bh=bTIm4kEPsxVq8BgShvZ+rZMrdO8fAi7PKD5V5Mehtpw=;
	b=DjabGQTGEX3MvgbwXWnrIy30/GM6a01dW8UbWYLRlh61udEH4Gqk/gDJ6KvUPPmOG5
	x+7ECdFgh4DfurTY8dMDX1PLnQOdhx9AEsEgNHtVxat35qABVQKcj+YGZWkz+7/4z80d
	c3EiKsOiPLtIjUPCqEkl2GE5Flfm2SOyrwEa4=
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=
Received: by 10.142.170.20 with SMTP id s20mr214099wfe.137.1246400662086;
	Tue, 30 Jun 2009 15:24:22 -0700 (PDT)
Received: from ?192.168.48.102? ([208.187.158.93])
	by mx.google.com with ESMTPS id 30sm1673140wfd.3.2009.06.30.15.24.18
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Tue, 30 Jun 2009 15:24:21 -0700 (PDT)
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200906/128
X-Sequence-Number: 32733

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?



From scrawford@pinpointresearch.com Tue Jun  30 22:39:28 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 6F4886335D1
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:39:28 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 22443-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:39:21 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from polaris.pinpointresearch.com
	(66-7-238-130.static-ip.telepacific.net [66.7.238.130])
	by mail.postgresql.org (Postfix) with ESMTP id 661BC6333C6
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 19:39:27 -0300 (ADT)
Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com
	[192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id 54FBAE00EC83;
	Tue, 30 Jun 2009 15:39:24 -0700 (PDT)
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.098 tagged_above=0 required=5 tests=AWL=0.098
X-Spam-Level: 
X-Archive-Number: 200906/130
X-Sequence-Number: 32735

...
>
>
>   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)





From spam_eater@gmx.net Tue Jun  30 22:41:54 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id D9761634247
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:41:54 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 22848-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 19:41:47 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by mail.postgresql.org (Postfix) with ESMTP id C86AB63423D
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 19:41:53 -0300 (ADT)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1MLm1O-0003b0-GA
	for pgsql-sql@postgresql.org; Tue, 30 Jun 2009 22:41:50 +0000
Received: from ppp-93-104-71-87.dynamic.mnet-online.de ([93.104.71.87])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 22:41:50 +0000
Received: from spam_eater by ppp-93-104-71-87.dynamic.mnet-online.de with
	local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 22:41:50 +0000
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: it's not NULL, then what is it?
Date: Wed, 01 Jul 2009 00:41:35 +0200
Lines: 16
Message-ID: <h2e4au$ans$1@ger.gmane.org>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<4A4A941C.2060604@pinpointresearch.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@ger.gmane.org
X-Gmane-NNTP-Posting-Host: ppp-93-104-71-87.dynamic.mnet-online.de
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <4A4A941C.2060604@pinpointresearch.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200906/131
X-Sequence-Number: 32736

Steve Crawford wrote on 01.07.2009 00:39:
>>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>>   canon-#  where maf ISNULL;
>>
> I believe count will only count not-null 

Correct

SELECT count(some_col)
FROM some_table;

is the same as

SELECT count(*)
FROM some_table
WHERE some_col IS NOT NULL;




From tsakai@gallo.ucsf.edu Wed Jul  1 00:55:45 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 90081632CCD
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 21:55:45 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 38488-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 21:55:43 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id DFEBB632307
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 21:55:43 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9F9E6.A7EF0C00"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it?
Date: Tue, 30 Jun 2009 17:55:41 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C02@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it?
Thread-Index: Acn50YUINdREktpPT3iq/8PhAiMUvgAEqFN+
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<4A4A908B.1080607@gmail.com>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Rob Sargent" <robjsargent@gmail.com>
Cc: <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/132
X-Sequence-Number: 32737

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9F9E6.A7EF0C00
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Rob,

> Maybe something like

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

It prints many (1,132,691 to be exact) lines consisting of 7 space
characters followed by many lines like:
 ]0.0106383[
 ]0.0106383[
 ]0.0106383[


> or

>     select length(maf::text)

This results in many lines of 7 space characters, followed by a
bunch of 9's, 10's, 8's...

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

Yes, me too.  But,

  canon=3D# select maf
  canon-#   from gallo.sds_seq_reg_shw
  canon-#  where maf =3D NAN;
  ERROR:  column "nan" does not exist
  LINE 3:  where maf =3D NAN;
                       ^
=20
What can I put to the right of equal sign to make the query work?

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Rob Sargent [mailto:robjsargent@gmail.com]
Sent: Tue 6/30/2009 3:24 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
=20
Tena Sakai wrote:
>
> Hi Everybody,
>
> I have a table called gallo.sds_seq_reg_shw,
> which is like:
>
>   canon=3D# \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    |
> =20
> I am intereseted in the column maf (which is real):
>
>   canon=3D# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf asc;
>        maf   =20
>   -------------
>    0.000659631
>    0.000659631
>    0.000659631
>    0.000659631
>         .
>   (trunacated for the interest of breivity)
>         .
>
> Another way to look at this column is:
>
>   canon=3D# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf desc;
>        maf   =20
>   -------------
>             =20
>             =20
>             =20
>         .
>   (trunacated for the interest of breivity)
>         .
>
> These rows shown are blanks, as far as I can tell.
> But...
>
>   canon=3D# select count(maf) from gallo.sds_seq_reg_shw;
>    count
>   -------
>    67284
>   (1 row)
> =20
>   canon=3D# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>    count
>   -------
>        0
>   (1 row)
> =20
>   canon=3D#
>   canon=3D# 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=20
environment?


------_=_NextPart_001_01C9F9E6.A7EF0C00
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Hi Rob,<BR>
<BR>
&gt; Maybe something like<BR>
<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; select ']' || maf::text || '[' -- just to =
see where the value<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; start/stops<BR>
<BR>
It prints many (1,132,691 to be exact) lines consisting of 7 space<BR>
characters followed by many lines like:<BR>
&nbsp;]0.0106383[<BR>
&nbsp;]0.0106383[<BR>
&nbsp;]0.0106383[<BR>
<BR>
<BR>
&gt; or<BR>
<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; select length(maf::text)<BR>
<BR>
This results in many lines of 7 space characters, followed by a<BR>
bunch of 9's, 10's, 8's...<BR>
<BR>
&gt; but I suspect you're getting NAN or something unprintable in =
your<BR>
&gt; environment?<BR>
<BR>
Yes, me too.&nbsp; But,<BR>
<BR>
&nbsp; canon=3D# select maf<BR>
&nbsp; canon-#&nbsp;&nbsp; from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; where maf =3D NAN;<BR>
&nbsp; ERROR:&nbsp; column &quot;nan&quot; does not exist<BR>
&nbsp; LINE 3:&nbsp; where maf =3D NAN;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ^<BR>
<BR>
What can I put to the right of 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=3D"mailto:robjsargent@gmail.com">mailto:robjsargent@gmail.com</A>]<B=
R>
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>
&gt;<BR>
&gt; Hi Everybody,<BR>
&gt;<BR>
&gt; I have a table called gallo.sds_seq_reg_shw,<BR>
&gt; which is like:<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# \d gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table =
&quot;gallo.sds_seq_reg_shw&quot;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; Type&nbsp;&nbsp; | =
Modifiers<BR>
&gt;&nbsp;&nbsp; ------------------+---------+-----------<BR>
&gt;&nbsp;&nbsp;&nbsp; =
name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
response&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp; | integer |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
source&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
| real&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; pvalue.term&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
real&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; stars.term&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; gtclass.test&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; fclass.test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; gtclass.ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; fclass.ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
markerid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | integer |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
maf&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | real&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; chromosome&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; physicalposition | integer |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;&nbsp;&nbsp; =
ctrast&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt;&nbsp;<BR>
&gt; I am intereseted in the column maf (which is real):<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp; canon-#&nbsp; order by maf asc;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maf&nbsp;&nbsp;&nbsp;<BR>
&gt;&nbsp;&nbsp; -------------<BR>
&gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt;&nbsp;&nbsp; (trunacated for the interest of breivity)<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt;<BR>
&gt; Another way to look at this column is:<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp; canon-#&nbsp; order by maf desc;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maf&nbsp;&nbsp;&nbsp;<BR>
&gt;&nbsp;&nbsp; -------------<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt;&nbsp;&nbsp; (trunacated for the interest of breivity)<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt;<BR>
&gt; These rows shown are blanks, as far as I can tell.<BR>
&gt; But...<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw;<BR>
&gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; -------<BR>
&gt;&nbsp;&nbsp;&nbsp; 67284<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;&nbsp;<BR>
&gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp; canon-#&nbsp; where maf ISNULL;<BR>
&gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; -------<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;&nbsp;<BR>
&gt;&nbsp;&nbsp; canon=3D#<BR>
&gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp; canon-#&nbsp; where maf NOTNULL;<BR>
&gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; -------<BR>
&gt;&nbsp;&nbsp;&nbsp; 67284<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;<BR>
&gt; My confusion is that if they are real and not null,<BR>
&gt; what are they?&nbsp; How would I construct a query to do<BR>
&gt; something like:<BR>
&gt;<BR>
&gt;&nbsp; select count(maf)<BR>
&gt;&nbsp;&nbsp;&nbsp; from gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp; where maf ISBLANK;<BR>
&gt;<BR>
&gt; Thank you in advance.<BR>
&gt;<BR>
&gt; Regards,<BR>
&gt;<BR>
&gt; Tena Sakai<BR>
&gt; tsakai@gallo.ucsf.edu<BR>
&gt;<BR>
Maybe something like<BR>
<BR>
&nbsp;&nbsp;&nbsp; select ']' || maf::text || '[' -- just to see where =
the value<BR>
&nbsp;&nbsp;&nbsp; start/stops<BR>
<BR>
or<BR>
<BR>
&nbsp;&nbsp;&nbsp; select length(maf::text)<BR>
<BR>
but I suspect you're getting NAN or something unprintable in your<BR>
environment?<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9F9E6.A7EF0C00--



From tsakai@gallo.ucsf.edu Wed Jul  1 00:57:51 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A05A4632BBF
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 21:57:51 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 39088-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 21:57:50 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id 27A0A632307
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 21:57:49 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9F9E6.F3EB75FC"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it?
Date: Tue, 30 Jun 2009 17:56:36 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C03@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it?
Thread-Index: Acn5zn9AixxL9PKuRYCgQ1BEOCyqSwAAqH3QAAVp+G0=
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<0ab801c9f9d1$376ad880$a6408980$@com>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Edward W. Rouse" <erouse@comsquared.com>,
	<pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/133
X-Sequence-Number: 32738

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9F9E6.F3EB75FC
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Edward,

> Just out of curiosity did you try maf =3D 0?

Yes, and this is what I get:

  canon=3D# select maf
  canon-#   from gallo.sds_seq_reg_shw
  canon-#  where maf =3D 0;
   maf=20
  -----
  (0 rows)

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Edward W. Rouse
Sent: Tue 6/30/2009 3:22 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
=20
Just out of curiosity did you try maf =3D 0?

=20

Edward W. Rouse

=20

From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tena Sakai
Sent: Tuesday, June 30, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] it's not NULL, then what is it?

=20

Hi Everybody,

I have a table called gallo.sds_seq_reg_shw,
which is like:

  canon=3D# \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    |
=20
I am intereseted in the column maf (which is real):

  canon=3D# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf asc;
       maf   =20
  -------------
   0.000659631
   0.000659631
   0.000659631
   0.000659631
        .
  (trunacated for the interest of breivity)
        .

Another way to look at this column is:

  canon=3D# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf desc;
       maf   =20
  -------------
            =20
            =20
            =20
        .
  (trunacated for the interest of breivity)
        .

These rows shown are blanks, as far as I can tell.
But...

  canon=3D# select count(maf) from gallo.sds_seq_reg_shw;
   count
  -------
   67284
  (1 row)
=20
  canon=3D# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf ISNULL;
   count
  -------
       0
  (1 row)
=20
  canon=3D#
  canon=3D# 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=20



------_=_NextPart_001_01C9F9E6.F3EB75FC
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Hi Edward,<BR>
<BR>
&gt; Just out of curiosity did you try maf =3D 0?<BR>
<BR>
Yes, and this is what I get:<BR>
<BR>
&nbsp; canon=3D# select maf<BR>
&nbsp; canon-#&nbsp;&nbsp; from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; where maf =3D 0;<BR>
&nbsp;&nbsp; maf<BR>
&nbsp; -----<BR>
&nbsp; (0 rows)<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: pgsql-sql-owner@postgresql.org on behalf of Edward W. Rouse<BR>
Sent: Tue 6/30/2009 3:22 PM<BR>
To: pgsql-sql@postgresql.org<BR>
Subject: Re: [SQL] it's not NULL, then what is it?<BR>
<BR>
Just out of curiosity did you try maf =3D 0?<BR>
<BR>
<BR>
<BR>
Edward W. Rouse<BR>
<BR>
<BR>
<BR>
From: pgsql-sql-owner@postgresql.org [<A =
HREF=3D"mailto:pgsql-sql-owner@postgresql.org">mailto:pgsql-sql-owner@pos=
tgresql.org</A>]<BR>
On Behalf Of Tena Sakai<BR>
Sent: Tuesday, June 30, 2009 6:03 PM<BR>
To: pgsql-sql@postgresql.org<BR>
Subject: [SQL] it's not NULL, then what is it?<BR>
<BR>
<BR>
<BR>
Hi Everybody,<BR>
<BR>
I have a table called gallo.sds_seq_reg_shw,<BR>
which is like:<BR>
<BR>
&nbsp; canon=3D# \d gallo.sds_seq_reg_shw<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table =
&quot;gallo.sds_seq_reg_shw&quot;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; Type&nbsp;&nbsp; | =
Modifiers<BR>
&nbsp; ------------------+---------+-----------<BR>
&nbsp;&nbsp; =
name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; | text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; response&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; =
n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp; | integer |<BR>
&nbsp;&nbsp; =
source&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; =
test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; | text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; =
ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; =
value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
| real&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; pvalue.term&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
real&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; stars.term&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; gtclass.test&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; fclass.test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; gtclass.ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; fclass.ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; markerid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
integer |<BR>
&nbsp;&nbsp; =
maf&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | real&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; chromosome&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; physicalposition | integer |<BR>
&nbsp;&nbsp; =
id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp; |<BR>
&nbsp;&nbsp; =
ctrast&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
<BR>
I am intereseted in the column maf (which is real):<BR>
<BR>
&nbsp; canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; order by maf asc;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maf&nbsp;&nbsp;&nbsp;<BR>
&nbsp; -------------<BR>
&nbsp;&nbsp; 0.000659631<BR>
&nbsp;&nbsp; 0.000659631<BR>
&nbsp;&nbsp; 0.000659631<BR>
&nbsp;&nbsp; 0.000659631<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&nbsp; (trunacated for the interest of breivity)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
<BR>
Another way to look at this column is:<BR>
<BR>
&nbsp; canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; order by maf desc;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maf&nbsp;&nbsp;&nbsp;<BR>
&nbsp; -------------<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<=
BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<=
BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<=
BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&nbsp; (trunacated for the interest of breivity)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
<BR>
These rows shown are blanks, as far as I can tell.<BR>
But...<BR>
<BR>
&nbsp; canon=3D# select count(maf) from gallo.sds_seq_reg_shw;<BR>
&nbsp;&nbsp; count<BR>
&nbsp; -------<BR>
&nbsp;&nbsp; 67284<BR>
&nbsp; (1 row)<BR>
<BR>
&nbsp; canon=3D# select count(maf) from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; where maf ISNULL;<BR>
&nbsp;&nbsp; count<BR>
&nbsp; -------<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<BR>
&nbsp; (1 row)<BR>
<BR>
&nbsp; canon=3D#<BR>
&nbsp; canon=3D# select count(maf) from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; where maf NOTNULL;<BR>
&nbsp;&nbsp; count<BR>
&nbsp; -------<BR>
&nbsp;&nbsp; 67284<BR>
&nbsp; (1 row)<BR>
<BR>
My confusion is that if they are real and not null,<BR>
what are they?&nbsp; How would I construct a query to do<BR>
something like:<BR>
<BR>
&nbsp;select count(maf)<BR>
&nbsp;&nbsp; from gallo.sds_seq_reg_shw<BR>
&nbsp; where maf ISBLANK;<BR>
<BR>
Thank you in advance.<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9F9E6.F3EB75FC--



From tsakai@gallo.ucsf.edu Wed Jul  1 01:02:11 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 8A87F632BBF
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 22:02:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 50410-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 22:02:01 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id AC944632307
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 22:02:09 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9F9E7.8E6571C4"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it?
Date: Tue, 30 Jun 2009 17:58:21 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C04@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it?
Thread-Index: Acn5056k5nBigNRUSCek5s3J4S8HaAAE2kPU
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<4A4A941C.2060604@pinpointresearch.com>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Steve Crawford" <scrawford@pinpointresearch.com>
Cc: <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.167 tagged_above=0 required=5 tests=AWL=0.166,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/134
X-Sequence-Number: 32739

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9F9E7.8E6571C4
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Steve,

> I believe count will only count not-null anyway
> so this will always return zero.

Understood.  But that doesn't help me...
What I need is a query expression that I can substitute
for isblabla below:

  select maf
    from gallo.sds_seq_reg_shw
   where maf isblabla;

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Tue 6/30/2009 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
=20
...
>
>
>   canon=3D# 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=20
return zero. Try count(*) instead of count(maf). Here's an example:

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

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

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

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




------_=_NextPart_001_01C9F9E7.8E6571C4
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Hi Steve,<BR>
<BR>
&gt; I believe count will only count not-null anyway<BR>
&gt; so this will always return zero.<BR>
<BR>
Understood.&nbsp; But that doesn't help me...<BR>
What I need is a query expression that I can substitute<BR>
for isblabla below:<BR>
<BR>
&nbsp; select maf<BR>
&nbsp;&nbsp;&nbsp; from gallo.sds_seq_reg_shw<BR>
&nbsp;&nbsp; where maf isblabla;<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: Steve Crawford [<A =
HREF=3D"mailto:scrawford@pinpointresearch.com">mailto:scrawford@pinpointr=
esearch.com</A>]<BR>
Sent: Tue 6/30/2009 3:39 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>
...<BR>
&gt;<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw<BR>
&gt;&nbsp;&nbsp; canon-#&nbsp; where maf ISNULL;<BR>
&gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; -------<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;<BR>
I believe count will only count not-null anyway so this will always<BR>
return zero. Try count(*) instead of count(maf). Here's an example:<BR>
<BR>
steve@[local]=3D&gt; select * from barr;<BR>
LOG:&nbsp; duration: 0.226 ms<BR>
&nbsp;a | b<BR>
---+---<BR>
&nbsp;a | b<BR>
&nbsp;c | d<BR>
&nbsp;&nbsp; | e<BR>
(3 rows)<BR>
<BR>
steve@[local]=3D&gt; select coalesce(a, 'a is null'), coalesce(b, 'b =
is<BR>
null') from barr;<BR>
LOG:&nbsp; duration: 0.283 ms<BR>
&nbsp;coalesce&nbsp; | coalesce<BR>
-----------+----------<BR>
&nbsp;a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | b<BR>
&nbsp;c&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | d<BR>
&nbsp;a is null | e<BR>
(3 rows)<BR>
<BR>
steve@[local]=3D&gt; select count(a) from barr;<BR>
LOG:&nbsp; duration: 0.339 ms<BR>
&nbsp;count<BR>
-------<BR>
&nbsp;&nbsp;&nbsp;&nbsp; 2<BR>
(1 row)<BR>
<BR>
steve@[local]=3D&gt; select count(*) from barr where a isnull;<BR>
LOG:&nbsp; duration: 0.350 ms<BR>
&nbsp;count<BR>
-------<BR>
&nbsp;&nbsp;&nbsp;&nbsp; 1<BR>
(1 row)<BR>
<BR>
<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9F9E7.8E6571C4--



From tsakai@gallo.ucsf.edu Wed Jul  1 02:09:43 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 0D5DC635307
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 23:09:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 62572-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 23:09:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id 5DD6F635449
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 23:09:41 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9F9F0.FD1C319C"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it?
Date: Tue, 30 Jun 2009 19:09:02 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it?
Thread-Index: Acn57iPD4dTEy7yCRwuLH80SmSRPugAAsN+m
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Osvaldo Kussama" <osvaldo.kussama@gmail.com>, <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.158 tagged_above=0 required=5 tests=AWL=0.157,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/135
X-Sequence-Number: 32740

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9F9F0.FD1C319C
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Osvaldo,

> Try:
> SELECT count(*) FROM gallo.sds_seq_reg_shw;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;

> Don't use count(maf), use count(*).

Indeed!

  canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw;
    count =20
  ---------
   4645647
  (1 row)

  canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS =
NULL;
    count =20
  ---------
   4578363
  (1 row)

  canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT =
NULL;
   count=20
  -------
   67284
  (1 row)

$ dc
4578363 67284 + p q
4645647
$=20

Many thanks, Osvald.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu




-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo.kussama@gmail.com]
Sent: Tue 6/30/2009 6:49 PM
To: Tena Sakai
Subject: Re: [SQL] it's not NULL, then what is it?
=20
2009/6/30 Tena Sakai <tsakai@gallo.ucsf.edu>:
> Hi Everybody,
>
> I have a table called gallo.sds_seq_reg_shw,
> which is like:
>
> =A0 canon=3D# \d gallo.sds_seq_reg_shw
> =A0=A0=A0=A0=A0=A0 Table "gallo.sds_seq_reg_shw"
> =A0=A0=A0=A0=A0=A0=A0 Column=A0=A0=A0=A0=A0 |=A0 Type=A0=A0 | =
Modifiers
> =A0 ------------------+---------+-----------
> =A0=A0 name=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 response=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 n=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer |
> =A0=A0 source=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 test=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 ref=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 value=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | real=A0=A0=A0 |
> =A0=A0 pvalue.term=A0=A0=A0=A0=A0 | real=A0=A0=A0 |
> =A0=A0 stars.term=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 gtclass.test=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 fclass.test=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 gtclass.ref=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 fclass.ref=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 markerid=A0=A0=A0=A0=A0=A0=A0=A0 | integer |
> =A0=A0 maf=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | real=A0=A0=A0 |
> =A0=A0 chromosome=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 physicalposition | integer |
> =A0=A0 id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
> =A0=A0 ctrast=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |
>
> I am intereseted in the column maf (which is real):
>
> =A0 canon=3D# select maf from gallo.sds_seq_reg_shw
> =A0 canon-#=A0 order by maf asc;
> =A0=A0=A0=A0=A0=A0 maf
> =A0 -------------
> =A0=A0 0.000659631
> =A0=A0 0.000659631
> =A0=A0 0.000659631
> =A0=A0 0.000659631
> =A0=A0=A0=A0=A0=A0=A0 .
> =A0 (trunacated for the interest of breivity)
> =A0=A0=A0=A0=A0=A0=A0 .
>
> Another way to look at this column is:
>
> =A0 canon=3D# select maf from gallo.sds_seq_reg_shw
> =A0 canon-#=A0 order by maf desc;
> =A0=A0=A0=A0=A0=A0 maf
> =A0 -------------
>
>
>
> =A0=A0=A0=A0=A0=A0=A0 .
> =A0 (trunacated for the interest of breivity)
> =A0=A0=A0=A0=A0=A0=A0 .
>
> These rows shown are blanks, as far as I can tell.
> But...
>
> =A0 canon=3D# select count(maf) from gallo.sds_seq_reg_shw;
> =A0=A0 count
> =A0 -------
> =A0=A0 67284
> =A0 (1 row)
>
> =A0 canon=3D# select count(maf) from gallo.sds_seq_reg_shw
> =A0 canon-#=A0 where maf ISNULL;
> =A0=A0 count
> =A0 -------
> =A0=A0=A0=A0=A0=A0 0
> =A0 (1 row)
>
> =A0 canon=3D#
> =A0 canon=3D# select count(maf) from gallo.sds_seq_reg_shw
> =A0 canon-#=A0 where maf NOTNULL;
> =A0=A0 count
> =A0 -------
> =A0=A0 67284
> =A0 (1 row)
>
> My confusion is that if they are real and not null,
> what are they?=A0 How would I construct a query to do
> something like:
>
> =A0select count(maf)
> =A0=A0 from gallo.sds_seq_reg_shw
> =A0 where maf ISBLANK;
>


Try:
SELECT count(*) FROM gallo.sds_seq_reg_shw;
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;

Don't use count(maf), use count(*).

Osvaldo


------_=_NextPart_001_01C9F9F0.FD1C319C
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Hi Osvaldo,<BR>
<BR>
&gt; Try:<BR>
&gt; SELECT count(*) FROM gallo.sds_seq_reg_shw;<BR>
&gt; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;<BR>
&gt; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT =
NULL;<BR>
<BR>
&gt; Don't use count(maf), use count(*).<BR>
<BR>
Indeed!<BR>
<BR>
&nbsp; canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw;<BR>
&nbsp;&nbsp;&nbsp; count&nbsp;<BR>
&nbsp; ---------<BR>
&nbsp;&nbsp; 4645647<BR>
&nbsp; (1 row)<BR>
<BR>
&nbsp; canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS =
NULL;<BR>
&nbsp;&nbsp;&nbsp; count&nbsp;<BR>
&nbsp; ---------<BR>
&nbsp;&nbsp; 4578363<BR>
&nbsp; (1 row)<BR>
<BR>
&nbsp; canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS =
NOT NULL;<BR>
&nbsp;&nbsp; count<BR>
&nbsp; -------<BR>
&nbsp;&nbsp; 67284<BR>
&nbsp; (1 row)<BR>
<BR>
$ dc<BR>
4578363 67284 + p q<BR>
4645647<BR>
$<BR>
<BR>
Many thanks, Osvald.<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: Osvaldo Kussama [<A =
HREF=3D"mailto:osvaldo.kussama@gmail.com">mailto:osvaldo.kussama@gmail.co=
m</A>]<BR>
Sent: Tue 6/30/2009 6:49 PM<BR>
To: Tena Sakai<BR>
Subject: Re: [SQL] it's not NULL, then what is it?<BR>
<BR>
2009/6/30 Tena Sakai &lt;tsakai@gallo.ucsf.edu&gt;:<BR>
&gt; Hi Everybody,<BR>
&gt;<BR>
&gt; I have a table called gallo.sds_seq_reg_shw,<BR>
&gt; which is like:<BR>
&gt;<BR>
&gt; =A0 canon=3D# \d gallo.sds_seq_reg_shw<BR>
&gt; =A0=A0=A0=A0=A0=A0 Table &quot;gallo.sds_seq_reg_shw&quot;<BR>
&gt; =A0=A0=A0=A0=A0=A0=A0 Column=A0=A0=A0=A0=A0 |=A0 Type=A0=A0 | =
Modifiers<BR>
&gt; =A0 ------------------+---------+-----------<BR>
&gt; =A0=A0 name=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 =
|<BR>
&gt; =A0=A0 response=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 n=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | integer =
|<BR>
&gt; =A0=A0 source=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 test=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 =
|<BR>
&gt; =A0=A0 ref=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 =
|<BR>
&gt; =A0=A0 value=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | real=A0=A0=A0 |<BR>
&gt; =A0=A0 pvalue.term=A0=A0=A0=A0=A0 | real=A0=A0=A0 |<BR>
&gt; =A0=A0 stars.term=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 gtclass.test=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 fclass.test=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 gtclass.ref=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 fclass.ref=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 markerid=A0=A0=A0=A0=A0=A0=A0=A0 | integer |<BR>
&gt; =A0=A0 maf=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | real=A0=A0=A0 =
|<BR>
&gt; =A0=A0 chromosome=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt; =A0=A0 physicalposition | integer |<BR>
&gt; =A0=A0 id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 =
|<BR>
&gt; =A0=A0 ctrast=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0 |<BR>
&gt;<BR>
&gt; I am intereseted in the column maf (which is real):<BR>
&gt;<BR>
&gt; =A0 canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&gt; =A0 canon-#=A0 order by maf asc;<BR>
&gt; =A0=A0=A0=A0=A0=A0 maf<BR>
&gt; =A0 -------------<BR>
&gt; =A0=A0 0.000659631<BR>
&gt; =A0=A0 0.000659631<BR>
&gt; =A0=A0 0.000659631<BR>
&gt; =A0=A0 0.000659631<BR>
&gt; =A0=A0=A0=A0=A0=A0=A0 .<BR>
&gt; =A0 (trunacated for the interest of breivity)<BR>
&gt; =A0=A0=A0=A0=A0=A0=A0 .<BR>
&gt;<BR>
&gt; Another way to look at this column is:<BR>
&gt;<BR>
&gt; =A0 canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&gt; =A0 canon-#=A0 order by maf desc;<BR>
&gt; =A0=A0=A0=A0=A0=A0 maf<BR>
&gt; =A0 -------------<BR>
&gt;<BR>
&gt;<BR>
&gt;<BR>
&gt; =A0=A0=A0=A0=A0=A0=A0 .<BR>
&gt; =A0 (trunacated for the interest of breivity)<BR>
&gt; =A0=A0=A0=A0=A0=A0=A0 .<BR>
&gt;<BR>
&gt; These rows shown are blanks, as far as I can tell.<BR>
&gt; But...<BR>
&gt;<BR>
&gt; =A0 canon=3D# select count(maf) from gallo.sds_seq_reg_shw;<BR>
&gt; =A0=A0 count<BR>
&gt; =A0 -------<BR>
&gt; =A0=A0 67284<BR>
&gt; =A0 (1 row)<BR>
&gt;<BR>
&gt; =A0 canon=3D# select count(maf) from gallo.sds_seq_reg_shw<BR>
&gt; =A0 canon-#=A0 where maf ISNULL;<BR>
&gt; =A0=A0 count<BR>
&gt; =A0 -------<BR>
&gt; =A0=A0=A0=A0=A0=A0 0<BR>
&gt; =A0 (1 row)<BR>
&gt;<BR>
&gt; =A0 canon=3D#<BR>
&gt; =A0 canon=3D# select count(maf) from gallo.sds_seq_reg_shw<BR>
&gt; =A0 canon-#=A0 where maf NOTNULL;<BR>
&gt; =A0=A0 count<BR>
&gt; =A0 -------<BR>
&gt; =A0=A0 67284<BR>
&gt; =A0 (1 row)<BR>
&gt;<BR>
&gt; My confusion is that if they are real and not null,<BR>
&gt; what are they?=A0 How would I construct a query to do<BR>
&gt; something like:<BR>
&gt;<BR>
&gt; =A0select count(maf)<BR>
&gt; =A0=A0 from gallo.sds_seq_reg_shw<BR>
&gt; =A0 where maf ISBLANK;<BR>
&gt;<BR>
<BR>
<BR>
Try:<BR>
SELECT count(*) FROM gallo.sds_seq_reg_shw;<BR>
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;<BR>
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;<BR>
<BR>
Don't use count(maf), use count(*).<BR>
<BR>
Osvaldo<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9F9F0.FD1C319C--



From tgl@sss.pgh.pa.us Wed Jul  1 02:17:57 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 2924B6346B2
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 23:17:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 64219-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 30 Jun 2009 23:17:41 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 2ADBE633A58
	for <pgsql-sql@postgresql.org>; Tue, 30 Jun 2009 23:17:50 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n612HfaT001146;
	Tue, 30 Jun 2009 22:17:41 -0400 (EDT)
To: "Tena Sakai" <tsakai@gallo.ucsf.edu>
cc: "Edward W. Rouse" <erouse@comsquared.com>, pgsql-sql@postgresql.org
Subject: Re: it's not NULL, then what is it? 
In-reply-to: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C03@egcrc-ex01.egcrc.org> 
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<0ab801c9f9d1$376ad880$a6408980$@com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C03@egcrc-ex01.egcrc.org>
Comments: In-reply-to "Tena Sakai" <tsakai@gallo.ucsf.edu>
	message dated "Tue, 30 Jun 2009 17:56:36 -0700"
Date: Tue, 30 Jun 2009 22:17:41 -0400
Message-ID: <1145.1246414661@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.087 tagged_above=0 required=5 tests=AWL=0.087
X-Spam-Level: 
X-Archive-Number: 200906/136
X-Sequence-Number: 32741

"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>>> My confusion is that if they are real and not null,
>>> what are they?

Good question.  So far as I can see these must be some value that
sprintf() is printing as spaces; but not NaN and not Infinity because
float4out checks for those first.  I would argue that this must be
a bug in sprintf.  What platform are you running on exactly?  And
for that matter, what PG version is this?

			regards, tom lane



From tsakai@gallo.ucsf.edu Wed Jul  1 03:44:19 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id C1A446337DE
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 00:44:19 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 81965-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 00:44:17 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id 17610632681
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 00:44:17 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9F9FE.352B47A4"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it? 
Date: Tue, 30 Jun 2009 20:40:14 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C08@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it? 
Thread-Index: Acn58iEmTyr8RxAvR/K2I8sGd5o5YAAC4P2p
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<0ab801c9f9d1$376ad880$a6408980$@com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C03@egcrc-ex01.egcrc.org>
	<1145.1246414661@sss.pgh.pa.us>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Edward W. Rouse" <erouse@comsquared.com>,
	<pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.149 tagged_above=0 required=5 tests=AWL=0.148,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200907/1
X-Sequence-Number: 32742

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9F9FE.352B47A4
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Tom,

> What platform are you running on exactly?

It is redhat linux running on Dell hardware.
uname -a returns:
Linux vixen.egcrc.org 2.6.9-78.0.1.ELsmp #1 SMP Tue Jul 22 18:01:05 EDT =
2008 x86_64 x86_64 x86_64 GNU/Linux

> And for that matter, what PG version is this?

It is 8.3.6.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tue 6/30/2009 7:17 PM
To: Tena Sakai
Cc: Edward W. Rouse; pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?=20
=20
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>>> My confusion is that if they are real and not null,
>>> what are they?

Good question.  So far as I can see these must be some value that
sprintf() is printing as spaces; but not NaN and not Infinity because
float4out checks for those first.  I would argue that this must be
a bug in sprintf.  What platform are you running on exactly?  And
for that matter, what PG version is this?

			regards, tom lane


------_=_NextPart_001_01C9F9FE.352B47A4
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it? </TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Hi Tom,<BR>
<BR>
&gt; What platform are you running on exactly?<BR>
<BR>
It is redhat linux running on Dell hardware.<BR>
uname -a returns:<BR>
Linux vixen.egcrc.org 2.6.9-78.0.1.ELsmp #1 SMP Tue Jul 22 18:01:05 EDT =
2008 x86_64 x86_64 x86_64 GNU/Linux<BR>
<BR>
&gt; And for that matter, what PG version is this?<BR>
<BR>
It is 8.3.6.<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
-----Original Message-----<BR>
From: Tom Lane [<A =
HREF=3D"mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</A>]<BR>
Sent: Tue 6/30/2009 7:17 PM<BR>
To: Tena Sakai<BR>
Cc: Edward W. Rouse; pgsql-sql@postgresql.org<BR>
Subject: Re: [SQL] it's not NULL, then what is it?<BR>
<BR>
&quot;Tena Sakai&quot; &lt;tsakai@gallo.ucsf.edu&gt; writes:<BR>
&gt;&gt;&gt; My confusion is that if they are real and not null,<BR>
&gt;&gt;&gt; what are they?<BR>
<BR>
Good question.&nbsp; So far as I can see these must be some value =
that<BR>
sprintf() is printing as spaces; but not NaN and not Infinity =
because<BR>
float4out checks for those first.&nbsp; I would argue that this must =
be<BR>
a bug in sprintf.&nbsp; What platform are you running on exactly?&nbsp; =
And<BR>
for that matter, what PG version is this?<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; regards, tom lane<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9F9FE.352B47A4--



From robjsargent@gmail.com Wed Jul  1 16:36:44 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id EFF66633224
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 13:36:44 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 83696-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 13:36:42 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-px0-f178.google.com (mail-px0-f178.google.com
	[209.85.216.178])
	by mail.postgresql.org (Postfix) with ESMTP id C5631633208
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 13:36:42 -0300 (ADT)
Received: by pxi8 with SMTP id 8so1207577pxi.29
	for <pgsql-sql@postgresql.org>; Wed, 01 Jul 2009 09:36:41 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from
	:user-agent:mime-version:to:cc:subject:references:in-reply-to
	:content-type:content-transfer-encoding;
	bh=ChuzDeL3ocvVTgFUvQ8kHSg1pLZBbEFKp9DZ4grarGQ=;
	b=SCp7RUznmWsP65TXNitsm7wqzluiimDcQemOLeSRIr/9gimwjK3Lj5OjwAkM2tHjR2
	iBV7wXBIMxh7yO31oOzRaGARShdPIXuoHoCiwOACcbczO/Bk9fL3fYpvXSnAxcLCVyDn
	pXxkED2h6xXk6Den9dwK03xzUMMiUskQoPxVY=
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=lTnxmNn2GfeB4YHKNYZ+w2UPDfBN8w2GCevTuYVkfSI6cVJ2Ziy7apH55DkTILKmeM
	UPCLzo8U03kKRW6BgjUy6dEJkMbj21TRhwjbgKubiLQ6so1YscsxOEZaT2cZh6Z3UDIM
	LnFGUnbDYiAyBWQM+n4GDcswCf8fRuRQd4Mls=
Received: by 10.114.37.1 with SMTP id k1mr15820677wak.194.1246466201481;
	Wed, 01 Jul 2009 09:36:41 -0700 (PDT)
Received: from ?192.168.0.9? (rsargent.dsl.xmission.com [199.104.125.126])
	by mx.google.com with ESMTPS id n6sm2371703wag.39.2009.07.01.09.36.39
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Wed, 01 Jul 2009 09:36:40 -0700 (PDT)
Message-ID: <4A4B9096.5020704@gmail.com>
Date: Wed, 01 Jul 2009 10:36:38 -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>
	<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200907/2
X-Sequence-Number: 32743

So they were null, and null turns out to be a seven-character blank string!?

Btw, you can change the displayed value of null with
    \pset null nil
and you will seem 4+ million 'nil's in your output


Tena Sakai wrote:
>
> Hi Osvaldo,
>
> > Try:
> > SELECT count(*) FROM gallo.sds_seq_reg_shw;
> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
>
> > Don't use count(maf), use count(*).
>
> Indeed!
>
>   canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw;
>     count 
>   ---------
>    4645647
>   (1 row)
>
>   canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
>     count 
>   ---------
>    4578363
>   (1 row)
>
>   canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT 
> NULL;
>    count
>   -------
>    67284
>   (1 row)
>
> $ dc
> 4578363 67284 + p q
> 4645647
> $
>
> Many thanks, Osvald.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
>
>
> -----Original Message-----
> From: Osvaldo Kussama [mailto:osvaldo.kussama@gmail.com]
> Sent: Tue 6/30/2009 6:49 PM
> To: Tena Sakai
> Subject: Re: [SQL] it's not NULL, then what is it?
>
> 2009/6/30 Tena Sakai <tsakai@gallo.ucsf.edu>:
> > 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;
> >
>
>
> Try:
> SELECT count(*) FROM gallo.sds_seq_reg_shw;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
>
> Don't use count(maf), use count(*).
>
> Osvaldo
>




From tsakai@gallo.ucsf.edu Wed Jul  1 17:02:32 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 059ED6350E4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:02:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 80638-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:02:30 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id 759246350D7
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 14:02:30 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9FA6D.B6EAC904"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it?
Date: Wed, 1 Jul 2009 10:02:28 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C0C@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it?
Thread-Index: Acn6ah2c5CKKdMhqRKiYOSisyrPLZAAAmyC/
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
	<4A4B9096.5020704@gmail.com>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Rob Sargent" <robjsargent@gmail.com>
Cc: <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200907/4
X-Sequence-Number: 32745

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9FA6D.B6EAC904
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Rob,

> So they were null,

Yes!

> and null turns out to be a seven-character blank string!?

I don't understand how that happens.  Mr Tom Lane
hinted that it might be a bug in sprintf...

> Btw, you can change the displayed value of null with
>     \pset null nil
> and you will seem 4+ million 'nil's in your output

That is an excellent trick/skill!

  canon=3D# \pset null nil
  Null display is "nil".
  canon=3D#=20
  canon=3D# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf desc
  canon-#  limit 10;
   maf=20
  -----
   nil
   nil
   nil
   nil
   nil
   nil
   nil
   nil
   nil
   nil
  (10 rows)

  canon=3D#=20

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

-----Original Message-----
From: Rob Sargent [mailto:robjsargent@gmail.com]
Sent: Wed 7/1/2009 9:36 AM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
=20
So they were null, and null turns out to be a seven-character blank =
string!?

Btw, you can change the displayed value of null with
    \pset null nil
and you will seem 4+ million 'nil's in your output


Tena Sakai wrote:
>
> Hi Osvaldo,
>
> > Try:
> > SELECT count(*) FROM gallo.sds_seq_reg_shw;
> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
>
> > Don't use count(maf), use count(*).
>
> Indeed!
>
>   canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw;
>     count=20
>   ---------
>    4645647
>   (1 row)
>
>   canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS =
NULL;
>     count=20
>   ---------
>    4578363
>   (1 row)
>
>   canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS =
NOT=20
> NULL;
>    count
>   -------
>    67284
>   (1 row)
>
> $ dc
> 4578363 67284 + p q
> 4645647
> $
>
> Many thanks, Osvald.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
>
>
> -----Original Message-----
> From: Osvaldo Kussama [mailto:osvaldo.kussama@gmail.com]
> Sent: Tue 6/30/2009 6:49 PM
> To: Tena Sakai
> Subject: Re: [SQL] it's not NULL, then what is it?
>
> 2009/6/30 Tena Sakai <tsakai@gallo.ucsf.edu>:
> > Hi Everybody,
> >
> > I have a table called gallo.sds_seq_reg_shw,
> > which is like:
> >
> >   canon=3D# \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=3D# 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=3D# 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=3D# select count(maf) from gallo.sds_seq_reg_shw;
> >    count
> >   -------
> >    67284
> >   (1 row)
> >
> >   canon=3D# select count(maf) from gallo.sds_seq_reg_shw
> >   canon-#  where maf ISNULL;
> >    count
> >   -------
> >        0
> >   (1 row)
> >
> >   canon=3D#
> >   canon=3D# 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;
> >
>
>
> Try:
> SELECT count(*) FROM gallo.sds_seq_reg_shw;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
>
> Don't use count(maf), use count(*).
>
> Osvaldo
>



------_=_NextPart_001_01C9FA6D.B6EAC904
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Hi Rob,<BR>
<BR>
&gt; So they were null,<BR>
<BR>
Yes!<BR>
<BR>
&gt; and null turns out to be a seven-character blank string!?<BR>
<BR>
I don't understand how that happens.&nbsp; Mr Tom Lane<BR>
hinted that it might be a bug in sprintf...<BR>
<BR>
&gt; Btw, you can change the displayed value of null with<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; \pset null nil<BR>
&gt; and you will seem 4+ million 'nil's in your output<BR>
<BR>
That is an excellent trick/skill!<BR>
<BR>
&nbsp; canon=3D# \pset null nil<BR>
&nbsp; Null display is &quot;nil&quot;.<BR>
&nbsp; canon=3D#<BR>
&nbsp; canon=3D# select maf from gallo.sds_seq_reg_shw<BR>
&nbsp; canon-#&nbsp; order by maf desc<BR>
&nbsp; canon-#&nbsp; limit 10;<BR>
&nbsp;&nbsp; maf<BR>
&nbsp; -----<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp;&nbsp; nil<BR>
&nbsp; (10 rows)<BR>
<BR>
&nbsp; canon=3D#<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
-----Original Message-----<BR>
From: Rob Sargent [<A =
HREF=3D"mailto:robjsargent@gmail.com">mailto:robjsargent@gmail.com</A>]<B=
R>
Sent: Wed 7/1/2009 9:36 AM<BR>
To: Tena Sakai<BR>
Cc: pgsql-sql@postgresql.org<BR>
Subject: Re: [SQL] it's not NULL, then what is it?<BR>
<BR>
So they were null, and null turns out to be a seven-character blank =
string!?<BR>
<BR>
Btw, you can change the displayed value of null with<BR>
&nbsp;&nbsp;&nbsp; \pset null nil<BR>
and you will seem 4+ million 'nil's in your output<BR>
<BR>
<BR>
Tena Sakai wrote:<BR>
&gt;<BR>
&gt; Hi Osvaldo,<BR>
&gt;<BR>
&gt; &gt; Try:<BR>
&gt; &gt; SELECT count(*) FROM gallo.sds_seq_reg_shw;<BR>
&gt; &gt; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS =
NULL;<BR>
&gt; &gt; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT =
NULL;<BR>
&gt;<BR>
&gt; &gt; Don't use count(maf), use count(*).<BR>
&gt;<BR>
&gt; Indeed!<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# SELECT count(*) FROM =
gallo.sds_seq_reg_shw;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; ---------<BR>
&gt;&nbsp;&nbsp;&nbsp; 4645647<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw =
WHERE maf IS NULL;<BR>
&gt;&nbsp;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; ---------<BR>
&gt;&nbsp;&nbsp;&nbsp; 4578363<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;<BR>
&gt;&nbsp;&nbsp; canon=3D# SELECT count(*) FROM gallo.sds_seq_reg_shw =
WHERE maf IS NOT<BR>
&gt; NULL;<BR>
&gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt;&nbsp;&nbsp; -------<BR>
&gt;&nbsp;&nbsp;&nbsp; 67284<BR>
&gt;&nbsp;&nbsp; (1 row)<BR>
&gt;<BR>
&gt; $ dc<BR>
&gt; 4578363 67284 + p q<BR>
&gt; 4645647<BR>
&gt; $<BR>
&gt;<BR>
&gt; Many thanks, Osvald.<BR>
&gt;<BR>
&gt; Regards,<BR>
&gt;<BR>
&gt; Tena Sakai<BR>
&gt; tsakai@gallo.ucsf.edu<BR>
&gt;<BR>
&gt;<BR>
&gt;<BR>
&gt;<BR>
&gt; -----Original Message-----<BR>
&gt; From: Osvaldo Kussama [<A =
HREF=3D"mailto:osvaldo.kussama@gmail.com">mailto:osvaldo.kussama@gmail.co=
m</A>]<BR>
&gt; Sent: Tue 6/30/2009 6:49 PM<BR>
&gt; To: Tena Sakai<BR>
&gt; Subject: Re: [SQL] it's not NULL, then what is it?<BR>
&gt;<BR>
&gt; 2009/6/30 Tena Sakai &lt;tsakai@gallo.ucsf.edu&gt;:<BR>
&gt; &gt; Hi Everybody,<BR>
&gt; &gt;<BR>
&gt; &gt; I have a table called gallo.sds_seq_reg_shw,<BR>
&gt; &gt; which is like:<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D# \d gallo.sds_seq_reg_shw<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table =
&quot;gallo.sds_seq_reg_shw&quot;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; Type&nbsp;&nbsp; | =
Modifiers<BR>
&gt; &gt;&nbsp;&nbsp; ------------------+---------+-----------<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
response&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp; | integer |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
source&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
| real&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; pvalue.term&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
real&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
stars.term&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp; =
|<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; gtclass.test&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; fclass.test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; gtclass.ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
fclass.ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp; =
|<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
markerid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | integer |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
maf&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | real&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
chromosome&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp; =
|<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; physicalposition | integer |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; =
ctrast&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
text&nbsp;&nbsp;&nbsp; |<BR>
&gt; &gt;<BR>
&gt; &gt; I am intereseted in the column maf (which is real):<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D# select maf from =
gallo.sds_seq_reg_shw<BR>
&gt; &gt;&nbsp;&nbsp; canon-#&nbsp; order by maf asc;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maf<BR>
&gt; &gt;&nbsp;&nbsp; -------------<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; 0.000659631<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt; &gt;&nbsp;&nbsp; (trunacated for the interest of breivity)<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt; &gt;<BR>
&gt; &gt; Another way to look at this column is:<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D# select maf from =
gallo.sds_seq_reg_shw<BR>
&gt; &gt;&nbsp;&nbsp; canon-#&nbsp; order by maf desc;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maf<BR>
&gt; &gt;&nbsp;&nbsp; -------------<BR>
&gt; &gt;<BR>
&gt; &gt;<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt; &gt;&nbsp;&nbsp; (trunacated for the interest of breivity)<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .<BR>
&gt; &gt;<BR>
&gt; &gt; These rows shown are blanks, as far as I can tell.<BR>
&gt; &gt; But...<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt; &gt;&nbsp;&nbsp; -------<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; 67284<BR>
&gt; &gt;&nbsp;&nbsp; (1 row)<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw<BR>
&gt; &gt;&nbsp;&nbsp; canon-#&nbsp; where maf ISNULL;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt; &gt;&nbsp;&nbsp; -------<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<BR>
&gt; &gt;&nbsp;&nbsp; (1 row)<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D#<BR>
&gt; &gt;&nbsp;&nbsp; canon=3D# select count(maf) from =
gallo.sds_seq_reg_shw<BR>
&gt; &gt;&nbsp;&nbsp; canon-#&nbsp; where maf NOTNULL;<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; count<BR>
&gt; &gt;&nbsp;&nbsp; -------<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; 67284<BR>
&gt; &gt;&nbsp;&nbsp; (1 row)<BR>
&gt; &gt;<BR>
&gt; &gt; My confusion is that if they are real and not null,<BR>
&gt; &gt; what are they?&nbsp; How would I construct a query to do<BR>
&gt; &gt; something like:<BR>
&gt; &gt;<BR>
&gt; &gt;&nbsp; select count(maf)<BR>
&gt; &gt;&nbsp;&nbsp;&nbsp; from gallo.sds_seq_reg_shw<BR>
&gt; &gt;&nbsp;&nbsp; where maf ISBLANK;<BR>
&gt; &gt;<BR>
&gt;<BR>
&gt;<BR>
&gt; Try:<BR>
&gt; SELECT count(*) FROM gallo.sds_seq_reg_shw;<BR>
&gt; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;<BR>
&gt; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT =
NULL;<BR>
&gt;<BR>
&gt; Don't use count(maf), use count(*).<BR>
&gt;<BR>
&gt; Osvaldo<BR>
&gt;<BR>
<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9FA6D.B6EAC904--



From tgl@sss.pgh.pa.us Wed Jul  1 17:42:31 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 44C4A6333D4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:42:31 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 98351-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:42:28 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 1142D633208
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 14:42:29 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n61HgR0R013654;
	Wed, 1 Jul 2009 13:42:27 -0400 (EDT)
To: "Tena Sakai" <tsakai@gallo.ucsf.edu>
cc: "Rob Sargent" <robjsargent@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: it's not NULL, then what is it? 
In-reply-to: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C0C@egcrc-ex01.egcrc.org> 
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
	<4A4B9096.5020704@gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C0C@egcrc-ex01.egcrc.org>
Comments: In-reply-to "Tena Sakai" <tsakai@gallo.ucsf.edu>
	message dated "Wed, 01 Jul 2009 10:02:28 -0700"
Date: Wed, 01 Jul 2009 13:42:27 -0400
Message-ID: <13653.1246470147@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.086 tagged_above=0 required=5 tests=AWL=0.086
X-Spam-Level: 
X-Archive-Number: 200907/6
X-Sequence-Number: 32747

"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> So they were null,

> Yes!

>> and null turns out to be a seven-character blank string!?

> I don't understand how that happens.  Mr Tom Lane
> hinted that it might be a bug in sprintf...

Well, that was before I read the messages where it turned out that they
were actually nulls after all.

			regards, tom lane



From tsakai@gallo.ucsf.edu Wed Jul  1 17:52:15 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3DECF634CF8
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:52:15 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 91259-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:52:14 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail12.egcrc.net (mail12.egcrc.net [63.193.204.12])
	by mail.postgresql.org (Postfix) with ESMTP id 423DA634839
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 14:52:14 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C9FA74.A9AA2334"
X-MimeOLE: Produced By Microsoft Exchange V6.5
Subject: Re: it's not NULL, then what is it? 
Date: Wed, 1 Jul 2009 10:47:36 -0700
Message-ID: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C0D@egcrc-ex01.egcrc.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] it's not NULL, then what is it? 
Thread-Index: Acn6c03dlK9V/Y7WSqezW0GPyDaqLwAALda2
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
	<4A4B9096.5020704@gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C0C@egcrc-ex01.egcrc.org>
	<13653.1246470147@sss.pgh.pa.us>
From: "Tena Sakai" <tsakai@gallo.ucsf.edu>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Rob Sargent" <robjsargent@gmail.com>,
	<pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200907/8
X-Sequence-Number: 32749

This is a multi-part message in MIME format.

------_=_NextPart_001_01C9FA74.A9AA2334
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Many thanks, Tom.

I wish I had known "\pset null nil" trick.
It would have saved a few unnecessary emails.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 7/1/2009 10:42 AM
To: Tena Sakai
Cc: Rob Sargent; pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?=20
=20
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> So they were null,

> Yes!

>> and null turns out to be a seven-character blank string!?

> I don't understand how that happens.  Mr Tom Lane
> hinted that it might be a bug in sprintf...

Well, that was before I read the messages where it turned out that they
were actually nulls after all.

			regards, tom lane


------_=_NextPart_001_01C9FA74.A9AA2334
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>RE: [SQL] it's not NULL, then what is it? </TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Many thanks, Tom.<BR>
<BR>
I wish I had known &quot;\pset null nil&quot; trick.<BR>
It would have saved a few unnecessary emails.<BR>
<BR>
Regards,<BR>
<BR>
Tena Sakai<BR>
tsakai@gallo.ucsf.edu<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: Tom Lane [<A =
HREF=3D"mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</A>]<BR>
Sent: Wed 7/1/2009 10:42 AM<BR>
To: Tena Sakai<BR>
Cc: Rob Sargent; pgsql-sql@postgresql.org<BR>
Subject: Re: [SQL] it's not NULL, then what is it?<BR>
<BR>
&quot;Tena Sakai&quot; &lt;tsakai@gallo.ucsf.edu&gt; writes:<BR>
&gt;&gt; So they were null,<BR>
<BR>
&gt; Yes!<BR>
<BR>
&gt;&gt; and null turns out to be a seven-character blank string!?<BR>
<BR>
&gt; I don't understand how that happens.&nbsp; Mr Tom Lane<BR>
&gt; hinted that it might be a bug in sprintf...<BR>
<BR>
Well, that was before I read the messages where it turned out that =
they<BR>
were actually nulls after all.<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; regards, tom lane<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C9FA74.A9AA2334--