DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=lTnxmNn2GfeB4YHKNYZ+w2UPDfBN8w2GCevTuYVkfSI6cVJ2Ziy7apH55DkTILKmeM
	UPCLzo8U03kKRW6BgjUy6dEJkMbj21TRhwjbgKubiLQ6so1YscsxOEZaT2cZh6Z3UDIM
	LnFGUnbDYiAyBWQM+n4GDcswCf8fRuRQd4Mls=
Message-ID: <4A4B9096.5020704@gmail.com>
Date: Wed, 01 Jul 2009 10:36:38 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Tena Sakai <tsakai@gallo.ucsf.edu>
CC: pgsql-sql@postgresql.org
Subject: Re: it's not NULL, then what is it?
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C01@egcrc-ex01.egcrc.org>
	<690707f60906301849s68eb934ck33db888c87a39087@mail.gmail.com>
	<FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84C07@egcrc-ex01.egcrc.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>