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=# \pset null nil Null display is "nil". canon=# canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf desc canon-# limit 10; maf ----- nil nil nil nil nil nil nil nil nil nil (10 rows) canon=# Regards, Tena Sakai tsa...@gallo.ucsf.edu -----Original Message----- From: Rob Sargent [mailto:robjsarg...@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? 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 > tsa...@gallo.ucsf.edu > > > > > -----Original Message----- > From: Osvaldo Kussama [mailto:osvaldo.kuss...@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 <tsa...@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 >
