Re: it's not NULL, then what is it?

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