From: Rob Sargent <robjsargent@gmail.com>
To: Tena Sakai <tsakai@gallo.ucsf.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: it's not NULL, then what is it?
Date: Wed, 01 Jul 2009 10:36:38 -0600
Message-ID: <4A4B9096.5020704@gmail.com> (raw)
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, tsakai@gallo.ucsf.edu
Subject: Re: it's not NULL, then what is it?
In-Reply-To: <4A4B9096.5020704@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox