Re: it's not NULL, then what is it? - Mailing list pgsql-sql
| From | Tena Sakai |
|---|---|
| Subject | Re: it's not NULL, then what is it? |
| Date | |
| Msg-id | FE44E0D7EAD2ED4BB2165071DB8E328C04E84C0C@egcrc-ex01.egcrc.org Whole thread Raw |
| In response to | it's not NULL, then what is it? ("Tena Sakai" <tsakai@gallo.ucsf.edu>) |
| Responses |
Re: it's not NULL, then what is it?
|
| List | pgsql-sql |
<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>