Thread: it's not NULL, then what is it?
<p><font size="2">Hi Everybody,<br /><br /> I have a table called gallo.sds_seq_reg_shw,<br /> which is like:<br /><br /> canon=# \d gallo.sds_seq_reg_shw<br /> Table "gallo.sds_seq_reg_shw"<br /> Column | Type | Modifiers<br/> ------------------+---------+-----------<br /> name | text |<br /> response | text |<br /> n | integer |<br /> source | text |<br /> test | text |<br /> ref | text |<br /> value | real |<br /> pvalue.term | real |<br /> stars.term | text |<br /> gtclass.test | text |<br /> fclass.test | text |<br /> gtclass.ref | text |<br /> fclass.ref | text |<br /> markerid | integer |<br /> maf | real |<br /> chromosome | text |<br /> physicalposition| integer |<br /> id | text |<br /> ctrast | text |<br /> <br /> I amintereseted in the column maf (which is real):<br /><br /> canon=# select maf from gallo.sds_seq_reg_shw<br /> canon-# order by maf asc;<br /> maf <br /> -------------<br /> 0.000659631<br /> 0.000659631<br /> 0.000659631<br/> 0.000659631<br /> .<br /> (trunacated for the interest of breivity)<br /> .<br /><br/> Another way to look at this column is:<br /><br /> canon=# select maf from gallo.sds_seq_reg_shw<br /> canon-# order by maf desc;<br /> maf <br /> -------------<br /> <br /> <br /> <br/> .<br /> (trunacated for the interest of breivity)<br /> .<br /><br /> These rows shownare blanks, as far as I can tell.<br /> But...<br /><br /> canon=# select count(maf) from gallo.sds_seq_reg_shw;<br/> count<br /> -------<br /> 67284<br /> (1 row)<br /> <br /> canon=# select count(maf)from gallo.sds_seq_reg_shw<br /> canon-# where maf ISNULL;<br /> count<br /> -------<br /> 0<br/> (1 row)<br /> <br /> canon=#<br /> canon=# select count(maf) from gallo.sds_seq_reg_shw<br /> canon-# wheremaf NOTNULL;<br /> count<br /> -------<br /> 67284<br /> (1 row)<br /><br /> My confusion is that if theyare real and not null,<br /> what are they? How would I construct a query to do<br /> something like:<br /><br /> selectcount(maf)<br /> from gallo.sds_seq_reg_shw<br /> where maf ISBLANK;<br /><br /> Thank you in advance.<br /><br/> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu</font>
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?
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
... > > > 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 msa | b ---+---a | bc | d | e (3 rows) steve@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is null') from barr; LOG: duration: 0.283 mscoalesce | coalesce -----------+----------a | bc | da is null | e (3 rows) steve@[local]=> select count(a) from barr; LOG: duration: 0.339 mscount ------- 2 (1 row) steve@[local]=> select count(*) from barr where a isnull; LOG: duration: 0.350 mscount ------- 1 (1 row)
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;
<p><font size="2">Hi Rob,<br /><br /> > Maybe something like<br /><br /> > select ']' || maf::text || '[' -- justto see where the value<br /> > start/stops<br /><br /> It prints many (1,132,691 to be exact) lines consistingof 7 space<br /> characters followed by many lines like:<br /> ]0.0106383[<br /> ]0.0106383[<br /> ]0.0106383[<br/><br /><br /> > or<br /><br /> > select length(maf::text)<br /><br /> This results in many linesof 7 space characters, followed by a<br /> bunch of 9's, 10's, 8's...<br /><br /> > but I suspect you're gettingNAN or something unprintable in your<br /> > environment?<br /><br /> Yes, me too. But,<br /><br /> canon=#select maf<br /> canon-# from gallo.sds_seq_reg_shw<br /> canon-# where maf = NAN;<br /> ERROR: column "nan"does not exist<br /> LINE 3: where maf = NAN;<br /> ^<br /><br /> What can I put to the rightof equal sign to make the query work?<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br/><br /> -----Original Message-----<br /> From: Rob Sargent [<a href="mailto:robjsargent@gmail.com">mailto:robjsargent@gmail.com</a>]<br/> Sent: Tue 6/30/2009 3:24 PM<br /> To: Tena Sakai<br/> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] it's not NULL, then what is it?<br /><br /> Tena Sakai wrote:<br/> ><br /> > Hi Everybody,<br /> ><br /> > I have a table called gallo.sds_seq_reg_shw,<br /> > whichis like:<br /> ><br /> > canon=# \d gallo.sds_seq_reg_shw<br /> > Table "gallo.sds_seq_reg_shw"<br/> > Column | Type | Modifiers<br /> > ------------------+---------+-----------<br/> > name | text |<br /> > response | text |<br /> > n | integer |<br /> > source | text |<br /> > test | text |<br /> > ref | text |<br /> > value | real |<br /> > pvalue.term | real |<br /> > stars.term | text |<br /> > gtclass.test | text |<br/> > fclass.test | text |<br /> > gtclass.ref | text |<br /> > fclass.ref | text |<br /> > markerid | integer |<br /> > maf | real |<br /> > chromosome | text |<br /> > physicalposition | integer |<br /> > id | text |<br /> > ctrast | text |<br /> > <br /> > I am intereseted in the column maf (which is real):<br /> ><br/> > canon=# select maf from gallo.sds_seq_reg_shw<br /> > canon-# order by maf asc;<br /> > maf <br /> > -------------<br /> > 0.000659631<br /> > 0.000659631<br /> > 0.000659631<br/> > 0.000659631<br /> > .<br /> > (trunacated for the interest of breivity)<br /> > .<br /> ><br /> > Another way to look at this column is:<br /> ><br /> > canon=# select maf fromgallo.sds_seq_reg_shw<br /> > canon-# order by maf desc;<br /> > maf <br /> > -------------<br/> > <br /> > <br /> > <br /> > .<br /> > (trunacatedfor the interest of breivity)<br /> > .<br /> ><br /> > These rows shown are blanks, as far asI can tell.<br /> > But...<br /> ><br /> > canon=# select count(maf) from gallo.sds_seq_reg_shw;<br /> > count<br /> > -------<br /> > 67284<br /> > (1 row)<br /> > <br /> > canon=# select count(maf)from gallo.sds_seq_reg_shw<br /> > canon-# where maf ISNULL;<br /> > count<br /> > -------<br/> > 0<br /> > (1 row)<br /> > <br /> > canon=#<br /> > canon=# select count(maf) fromgallo.sds_seq_reg_shw<br /> > canon-# where maf NOTNULL;<br /> > count<br /> > -------<br /> > 67284<br /> > (1 row)<br /> ><br /> > My confusion is that if they are real and not null,<br /> > whatare they? How would I construct a query to do<br /> > something like:<br /> ><br /> > select count(maf)<br/> > from gallo.sds_seq_reg_shw<br /> > where maf ISBLANK;<br /> ><br /> > Thank you in advance.<br/> ><br /> > Regards,<br /> ><br /> > Tena Sakai<br /> > tsakai@gallo.ucsf.edu<br /> ><br />Maybe something like<br /><br /> select ']' || maf::text || '[' -- just to see where the value<br /> start/stops<br/><br /> or<br /><br /> select length(maf::text)<br /><br /> but I suspect you're getting NAN or somethingunprintable in your<br /> environment?<br /><br /></font>
<p><font size="2">Hi Edward,<br /><br /> > Just out of curiosity did you try maf = 0?<br /><br /> Yes, and this is whatI get:<br /><br /> canon=# select maf<br /> canon-# from gallo.sds_seq_reg_shw<br /> canon-# where maf = 0;<br/> maf<br /> -----<br /> (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 ofEdward W. Rouse<br /> Sent: Tue 6/30/2009 3:22 PM<br /> To: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] it's notNULL, then what is it?<br /><br /> Just out of curiosity did you try maf = 0?<br /><br /><br /><br /> Edward W. Rouse<br/><br /><br /><br /> From: pgsql-sql-owner@postgresql.org [<a href="mailto:pgsql-sql-owner@postgresql.org">mailto:pgsql-sql-owner@postgresql.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 isit?<br /><br /><br /><br /> Hi Everybody,<br /><br /> I have a table called gallo.sds_seq_reg_shw,<br /> which is like:<br/><br /> canon=# \d gallo.sds_seq_reg_shw<br /> Table "gallo.sds_seq_reg_shw"<br /> Column | Type | Modifiers<br /> ------------------+---------+-----------<br /> name | text |<br/> response | text |<br /> n | integer |<br /> source | text |<br /> test | text |<br /> ref | text |<br /> value | real |<br /> pvalue.term | real |<br /> stars.term | text |<br /> gtclass.test | text |<br /> fclass.test | text |<br /> gtclass.ref | text |<br /> fclass.ref | text |<br /> markerid | integer |<br /> maf | real |<br /> chromosome | text |<br /> physicalposition| integer |<br /> id | text |<br /> ctrast | text |<br /><br /> I amintereseted in the column maf (which is real):<br /><br /> canon=# select maf from gallo.sds_seq_reg_shw<br /> canon-# order by maf asc;<br /> maf <br /> -------------<br /> 0.000659631<br /> 0.000659631<br /> 0.000659631<br/> 0.000659631<br /> .<br /> (trunacated for the interest of breivity)<br /> .<br /><br/> Another way to look at this column is:<br /><br /> canon=# select maf from gallo.sds_seq_reg_shw<br /> canon-# order by maf desc;<br /> maf <br /> -------------<br /> <br /> <br /> <br/> .<br /> (trunacated for the interest of breivity)<br /> .<br /><br /> These rows shownare blanks, as far as I can tell.<br /> But...<br /><br /> canon=# select count(maf) from gallo.sds_seq_reg_shw;<br/> count<br /> -------<br /> 67284<br /> (1 row)<br /><br /> canon=# select count(maf)from gallo.sds_seq_reg_shw<br /> canon-# where maf ISNULL;<br /> count<br /> -------<br /> 0<br/> (1 row)<br /><br /> canon=#<br /> canon=# select count(maf) from gallo.sds_seq_reg_shw<br /> canon-# wheremaf NOTNULL;<br /> count<br /> -------<br /> 67284<br /> (1 row)<br /><br /> My confusion is that if theyare real and not null,<br /> what are they? How would I construct a query to do<br /> something like:<br /><br /> selectcount(maf)<br /> from gallo.sds_seq_reg_shw<br /> where maf ISBLANK;<br /><br /> Thank you in advance.<br /><br/> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br /></font>
<p><font size="2">Hi Steve,<br /><br /> > I believe count will only count not-null anyway<br /> > so this will alwaysreturn zero.<br /><br /> Understood. But that doesn't help me...<br /> What I need is a query expression that I cansubstitute<br /> for isblabla below:<br /><br /> select maf<br /> from gallo.sds_seq_reg_shw<br /> where mafisblabla;<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br /> -----Original Message-----<br/> From: Steve Crawford [<a href="mailto:scrawford@pinpointresearch.com">mailto:scrawford@pinpointresearch.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 /> ><br /> ><br /> > canon=# select count(maf) from gallo.sds_seq_reg_shw<br /> > canon-# where maf ISNULL;<br /> > count<br /> > -------<br /> > 0<br /> > (1 row)<br /> ><br/> I believe count will only count not-null anyway so this will always<br /> return zero. Try count(*) instead ofcount(maf). Here's an example:<br /><br /> steve@[local]=> select * from barr;<br /> LOG: duration: 0.226 ms<br /> a | b<br /> ---+---<br /> a | b<br /> c | d<br /> | e<br /> (3 rows)<br /><br /> steve@[local]=> select coalesce(a,'a is null'), coalesce(b, 'b is<br /> null') from barr;<br /> LOG: duration: 0.283 ms<br /> coalesce | coalesce<br/> -----------+----------<br /> a | b<br /> c | d<br /> a is null | e<br /> (3 rows)<br /><br/> steve@[local]=> select count(a) from barr;<br /> LOG: duration: 0.339 ms<br /> count<br /> -------<br /> 2<br /> (1 row)<br /><br /> steve@[local]=> select count(*) from barr where a isnull;<br /> LOG: duration: 0.350ms<br /> count<br /> -------<br /> 1<br /> (1 row)<br /><br /><br /><br /></font>
<p><font size="2">Hi Osvaldo,<br /><br /> > Try:<br /> > SELECT count(*) FROM gallo.sds_seq_reg_shw;<br /> > SELECTcount(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;<br /> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHEREmaf IS NOT NULL;<br /><br /> > Don't use count(maf), use count(*).<br /><br /> Indeed!<br /><br /> canon=# SELECTcount(*) FROM gallo.sds_seq_reg_shw;<br /> count <br /> ---------<br /> 4645647<br /> (1 row)<br /><br /> canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;<br /> count <br /> ---------<br /> 4578363<br/> (1 row)<br /><br /> canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;<br /> count<br/> -------<br /> 67284<br /> (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="mailto:osvaldo.kussama@gmail.com">mailto:osvaldo.kussama@gmail.com</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 <tsakai@gallo.ucsf.edu>:<br/> > Hi Everybody,<br /> ><br /> > I have a table called gallo.sds_seq_reg_shw,<br/> > which is like:<br /> ><br /> > canon=# \d gallo.sds_seq_reg_shw<br /> > Table "gallo.sds_seq_reg_shw"<br /> > Column | Type | Modifiers<br /> > ------------------+---------+-----------<br/> > name | text |<br /> > response | text |<br /> > n | integer |<br /> > source | text |<br /> > test | text |<br /> > ref | text |<br /> > value | real |<br /> > pvalue.term | real |<br /> > stars.term | text |<br /> > gtclass.test | text |<br/> > fclass.test | text |<br /> > gtclass.ref | text |<br /> > fclass.ref | text |<br /> > markerid | integer |<br /> > maf | real |<br /> > chromosome | text |<br /> > physicalposition | integer |<br /> > id | text |<br /> > ctrast | text |<br /> ><br /> > I am intereseted in the column maf (which is real):<br /> ><br/> > canon=# select maf from gallo.sds_seq_reg_shw<br /> > canon-# order by maf asc;<br /> > maf<br /> > -------------<br /> > 0.000659631<br /> > 0.000659631<br /> > 0.000659631<br />> 0.000659631<br /> > .<br /> > (trunacated for the interest of breivity)<br /> > .<br/> ><br /> > Another way to look at this column is:<br /> ><br /> > canon=# select maf from gallo.sds_seq_reg_shw<br/> > canon-# order by maf desc;<br /> > maf<br /> > -------------<br /> ><br/> ><br /> ><br /> > .<br /> > (trunacated for the interest of breivity)<br /> > .<br /> ><br /> > These rows shown are blanks, as far as I can tell.<br /> > But...<br /> ><br /> > canon=# select count(maf) from gallo.sds_seq_reg_shw;<br /> > count<br /> > -------<br /> > 67284<br/> > (1 row)<br /> ><br /> > canon=# select count(maf) from gallo.sds_seq_reg_shw<br /> > canon-# where maf ISNULL;<br /> > count<br /> > -------<br /> > 0<br /> > (1 row)<br /> ><br/> > canon=#<br /> > canon=# select count(maf) from gallo.sds_seq_reg_shw<br /> > canon-# where mafNOTNULL;<br /> > count<br /> > -------<br /> > 67284<br /> > (1 row)<br /> ><br /> > My confusionis that if they are real and not null,<br /> > what are they? How would I construct a query to do<br /> >something like:<br /> ><br /> > select count(maf)<br /> > from gallo.sds_seq_reg_shw<br /> > wheremaf ISBLANK;<br /> ><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>
"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
<p><font size="2">Hi Tom,<br /><br /> > What platform are you running on exactly?<br /><br /> It is redhat linux runningon 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:05EDT 2008 x86_64 x86_64 x86_64 GNU/Linux<br /><br /> > 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="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]<br /> Sent: Tue 6/30/20097:17 PM<br /> To: Tena Sakai<br /> Cc: Edward W. Rouse; pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] it's notNULL, then what is it?<br /><br /> "Tena Sakai" <tsakai@gallo.ucsf.edu> writes:<br /> >>> My confusionis that if they are real and not null,<br /> >>> what are they?<br /><br /> Good question. So far as Ican 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. I would argue that this must be<br /> a bug in sprintf. What platform are you runningon exactly? And<br /> for that matter, what PG version is this?<br /><br /> regards, tomlane<br /><br /></font>
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 >
<p><font size="2">Hi Rob,<br /><br /> > So they were null,<br /><br /> Yes!<br /><br /> > and null turns out to bea seven-character blank string!?<br /><br /> I don't understand how that happens. Mr Tom Lane<br /> hinted that it mightbe a bug in sprintf...<br /><br /> > Btw, you can change the displayed value of null with<br /> > \pset nullnil<br /> > and you will seem 4+ million 'nil's in your output<br /><br /> That is an excellent trick/skill!<br /><br/> canon=# \pset null nil<br /> Null display is "nil".<br /> canon=#<br /> canon=# select maf from gallo.sds_seq_reg_shw<br/> canon-# order by maf desc<br /> canon-# limit 10;<br /> maf<br /> -----<br /> nil<br/> nil<br /> nil<br /> nil<br /> nil<br /> nil<br /> nil<br /> nil<br /> nil<br /> nil<br/> (10 rows)<br /><br /> canon=#<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br/> -----Original Message-----<br /> From: Rob Sargent [<a href="mailto:robjsargent@gmail.com">mailto:robjsargent@gmail.com</a>]<br/> 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 werenull, and null turns out to be a seven-character blank string!?<br /><br /> Btw, you can change the displayed value ofnull with<br /> \pset null nil<br /> and you will seem 4+ million 'nil's in your output<br /><br /><br /> Tena Sakaiwrote:<br /> ><br /> > Hi Osvaldo,<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 /> > > SELECTcount(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;<br /> ><br /> > > Don't use count(maf), use count(*).<br/> ><br /> > Indeed!<br /> ><br /> > canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw;<br />> count<br /> > ---------<br /> > 4645647<br /> > (1 row)<br /> ><br /> > canon=# SELECTcount(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;<br /> > count<br /> > ---------<br /> > 4578363<br/> > (1 row)<br /> ><br /> > canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT<br/> > NULL;<br /> > count<br /> > -------<br /> > 67284<br /> > (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="mailto:osvaldo.kussama@gmail.com">mailto:osvaldo.kussama@gmail.com</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 TenaSakai <tsakai@gallo.ucsf.edu>:<br /> > > Hi Everybody,<br /> > ><br /> > > I have a table calledgallo.sds_seq_reg_shw,<br /> > > which is like:<br /> > ><br /> > > canon=# \d gallo.sds_seq_reg_shw<br/> > > Table "gallo.sds_seq_reg_shw"<br /> > > Column | Type |Modifiers<br /> > > ------------------+---------+-----------<br /> > > name | text |<br/> > > response | text |<br /> > > n | integer |<br /> > > source | text |<br /> > > test | text |<br /> > > ref | text |<br /> > > value | real |<br /> > > pvalue.term | real |<br /> > > stars.term | text |<br /> > > gtclass.test | text |<br /> > > fclass.test |text |<br /> > > gtclass.ref | text |<br /> > > fclass.ref | text |<br /> > > markerid | integer |<br /> > > maf | real |<br /> > > chromosome |text |<br /> > > physicalposition | integer |<br /> > > id | text |<br /> > > ctrast | text |<br /> > ><br /> > > I am intereseted in the column maf (which is real):<br/> > ><br /> > > canon=# select maf from gallo.sds_seq_reg_shw<br /> > > canon-# order bymaf asc;<br /> > > maf<br /> > > -------------<br /> > > 0.000659631<br /> > > 0.000659631<br/> > > 0.000659631<br /> > > 0.000659631<br /> > > .<br /> > > (trunacatedfor the interest of breivity)<br /> > > .<br /> > ><br /> > > Another way to look atthis column is:<br /> > ><br /> > > canon=# select maf from gallo.sds_seq_reg_shw<br /> > > canon-# order by maf desc;<br /> > > maf<br /> > > -------------<br /> > ><br /> > ><br/> > ><br /> > > .<br /> > > (trunacated for the interest of breivity)<br /> > > .<br /> > ><br /> > > These rows shown are blanks, as far as I can tell.<br /> > > But...<br/> > ><br /> > > canon=# select count(maf) from gallo.sds_seq_reg_shw;<br /> > > count<br/> > > -------<br /> > > 67284<br /> > > (1 row)<br /> > ><br /> > > canon=#select count(maf) from gallo.sds_seq_reg_shw<br /> > > canon-# where maf ISNULL;<br /> > > count<br/> > > -------<br /> > > 0<br /> > > (1 row)<br /> > ><br /> > > canon=#<br/> > > canon=# select count(maf) from gallo.sds_seq_reg_shw<br /> > > canon-# where maf NOTNULL;<br/> > > count<br /> > > -------<br /> > > 67284<br /> > > (1 row)<br /> >><br /> > > My confusion is that if they are real and not null,<br /> > > what are they? How would Iconstruct a query to do<br /> > > something like:<br /> > ><br /> > > select count(maf)<br /> > > from gallo.sds_seq_reg_shw<br /> > > where maf ISBLANK;<br /> > ><br /> ><br /> ><br /> > Try:<br/> > SELECT count(*) FROM gallo.sds_seq_reg_shw;<br /> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE mafIS NULL;<br /> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;<br /> ><br /> > Don't usecount(maf), use count(*).<br /> ><br /> > Osvaldo<br /> ><br /><br /><br /></font>
"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
<p><font size="2">Many thanks, Tom.<br /><br /> I wish I had known "\pset null nil" trick.<br /> It would have saved a fewunnecessary emails.<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br /> -----OriginalMessage-----<br /> From: Tom Lane [<a href="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 /> "Tena Sakai" <tsakai@gallo.ucsf.edu> writes:<br /> >> So theywere null,<br /><br /> > Yes!<br /><br /> >> and null turns out to be a seven-character blank string!?<br /><br/> > I don't understand how that happens. Mr Tom Lane<br /> > 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 afterall.<br /><br /> regards, tom lane<br /><br /></font>