pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedit's not NULL, then what is it?
15+ messages / 6 participants
[nested] [flat]
* it's not NULL, then what is it?
@ 2009-06-30 22:02 Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 4 replies; 15+ messages in thread
From: Tena Sakai @ 2009-06-30 22:02 UTC (permalink / raw)
To: pgsql-sql
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;
Thank you in advance.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-06-30 22:22 Edward W. Rouse <erouse@comsquared.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
3 siblings, 1 reply; 15+ messages in thread
From: Edward W. Rouse @ 2009-06-30 22:22 UTC (permalink / raw)
To: pgsql-sql
Just out of curiosity did you try maf = 0?
Edward W. Rouse
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tena Sakai
Sent: Tuesday, June 30, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] it's not NULL, then what is it?
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;
Thank you in advance.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-06-30 22:24 Rob Sargent <robjsargent@gmail.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
3 siblings, 1 reply; 15+ messages in thread
From: Rob Sargent @ 2009-06-30 22:24 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-sql
Tena Sakai wrote:
>
> 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;
>
> Thank you in advance.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
Maybe something like
select ']' || maf::text || '[' -- just to see where the value
start/stops
or
select length(maf::text)
but I suspect you're getting NAN or something unprintable in your
environment?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-06-30 22:39 Steve Crawford <scrawford@pinpointresearch.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
3 siblings, 2 replies; 15+ messages in thread
From: Steve Crawford @ 2009-06-30 22:39 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-sql
...
>
>
> canon=# select count(maf) from gallo.sds_seq_reg_shw
> canon-# where maf ISNULL;
> count
> -------
> 0
> (1 row)
>
I believe count will only count not-null anyway so this will always
return zero. Try count(*) instead of count(maf). Here's an example:
steve@[local]=> select * from barr;
LOG: duration: 0.226 ms
a | b
---+---
a | b
c | d
| e
(3 rows)
steve@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is
null') from barr;
LOG: duration: 0.283 ms
coalesce | coalesce
-----------+----------
a | b
c | d
a is null | e
(3 rows)
steve@[local]=> select count(a) from barr;
LOG: duration: 0.339 ms
count
-------
2
(1 row)
steve@[local]=> select count(*) from barr where a isnull;
LOG: duration: 0.350 ms
count
-------
1
(1 row)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-06-30 22:41 Thomas Kellerer <spam_eater@gmx.net>
parent: Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 15+ messages in thread
From: Thomas Kellerer @ 2009-06-30 22:41 UTC (permalink / raw)
To: pgsql-sql
Steve Crawford wrote on 01.07.2009 00:39:
>> canon=# select count(maf) from gallo.sds_seq_reg_shw
>> canon-# where maf ISNULL;
>>
> I believe count will only count not-null
Correct
SELECT count(some_col)
FROM some_table;
is the same as
SELECT count(*)
FROM some_table
WHERE some_col IS NOT NULL;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 00:55 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 00:55 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
Hi Rob,
> Maybe something like
> select ']' || maf::text || '[' -- just to see where the value
> start/stops
It prints many (1,132,691 to be exact) lines consisting of 7 space
characters followed by many lines like:
]0.0106383[
]0.0106383[
]0.0106383[
> or
> select length(maf::text)
This results in many lines of 7 space characters, followed by a
bunch of 9's, 10's, 8's...
> but I suspect you're getting NAN or something unprintable in your
> environment?
Yes, me too. But,
canon=# select maf
canon-# from gallo.sds_seq_reg_shw
canon-# where maf = NAN;
ERROR: column "nan" does not exist
LINE 3: where maf = NAN;
^
What can I put to the right of equal sign to make the query work?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Rob Sargent [mailto:robjsargent@gmail.com]
Sent: Tue 6/30/2009 3:24 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
Tena Sakai wrote:
>
> 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;
>
> Thank you in advance.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
Maybe something like
select ']' || maf::text || '[' -- just to see where the value
start/stops
or
select length(maf::text)
but I suspect you're getting NAN or something unprintable in your
environment?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 00:56 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Edward W. Rouse <erouse@comsquared.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 00:56 UTC (permalink / raw)
To: Edward W. Rouse <erouse@comsquared.com>; pgsql-sql
Hi Edward,
> Just out of curiosity did you try maf = 0?
Yes, and this is what I get:
canon=# select maf
canon-# from gallo.sds_seq_reg_shw
canon-# where maf = 0;
maf
-----
(0 rows)
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Edward W. Rouse
Sent: Tue 6/30/2009 3:22 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
Just out of curiosity did you try maf = 0?
Edward W. Rouse
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tena Sakai
Sent: Tuesday, June 30, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] it's not NULL, then what is it?
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;
Thank you in advance.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 00:58 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 00:58 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-sql
Hi Steve,
> I believe count will only count not-null anyway
> so this will always return zero.
Understood. But that doesn't help me...
What I need is a query expression that I can substitute
for isblabla below:
select maf
from gallo.sds_seq_reg_shw
where maf isblabla;
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Tue 6/30/2009 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
...
>
>
> canon=# select count(maf) from gallo.sds_seq_reg_shw
> canon-# where maf ISNULL;
> count
> -------
> 0
> (1 row)
>
I believe count will only count not-null anyway so this will always
return zero. Try count(*) instead of count(maf). Here's an example:
steve@[local]=> select * from barr;
LOG: duration: 0.226 ms
a | b
---+---
a | b
c | d
| e
(3 rows)
steve@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is
null') from barr;
LOG: duration: 0.283 ms
coalesce | coalesce
-----------+----------
a | b
c | d
a is null | e
(3 rows)
steve@[local]=> select count(a) from barr;
LOG: duration: 0.339 ms
count
-------
2
(1 row)
steve@[local]=> select count(*) from barr where a isnull;
LOG: duration: 0.350 ms
count
-------
1
(1 row)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 02:09 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
3 siblings, 1 reply; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 02:09 UTC (permalink / raw)
To: Osvaldo Kussama <osvaldo.kussama@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 02:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2009-07-01 02:17 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: Edward W. Rouse <erouse@comsquared.com>; pgsql-sql
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>>> My confusion is that if they are real and not null,
>>> what are they?
Good question. So far as I can see these must be some value that
sprintf() is printing as spaces; but not NaN and not Infinity because
float4out checks for those first. I would argue that this must be
a bug in sprintf. What platform are you running on exactly? And
for that matter, what PG version is this?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 03:40 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 03:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Edward W. Rouse <erouse@comsquared.com>; pgsql-sql
Hi Tom,
> What platform are you running on exactly?
It is redhat linux running on Dell hardware.
uname -a returns:
Linux vixen.egcrc.org 2.6.9-78.0.1.ELsmp #1 SMP Tue Jul 22 18:01:05 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
> And for that matter, what PG version is this?
It is 8.3.6.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tue 6/30/2009 7:17 PM
To: Tena Sakai
Cc: Edward W. Rouse; pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>>> My confusion is that if they are real and not null,
>>> what are they?
Good question. So far as I can see these must be some value that
sprintf() is printing as spaces; but not NaN and not Infinity because
float4out checks for those first. I would argue that this must be
a bug in sprintf. What platform are you running on exactly? And
for that matter, what PG version is this?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 16:36 Rob Sargent <robjsargent@gmail.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Rob Sargent @ 2009-07-01 16:36 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 17:02 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 17:02 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
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
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Rob Sargent [mailto:robjsargent@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
> 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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 17:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2009-07-01 17:42 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> 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...
Well, that was before I read the messages where it turned out that they
were actually nulls after all.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: it's not NULL, then what is it?
@ 2009-07-01 17:47 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tena Sakai @ 2009-07-01 17:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
Many thanks, Tom.
I wish I had known "\pset null nil" trick.
It would have saved a few unnecessary emails.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 7/1/2009 10:42 AM
To: Tena Sakai
Cc: Rob Sargent; pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> 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...
Well, that was before I read the messages where it turned out that they
were actually nulls after all.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2009-07-01 17:47 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-30 22:02 it's not NULL, then what is it? Tena Sakai <tsakai@gallo.ucsf.edu>
2009-06-30 22:22 ` Edward W. Rouse <erouse@comsquared.com>
2009-07-01 00:56 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2009-07-01 02:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-07-01 03:40 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2009-06-30 22:24 ` Rob Sargent <robjsargent@gmail.com>
2009-07-01 00:55 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2009-06-30 22:39 ` Steve Crawford <scrawford@pinpointresearch.com>
2009-06-30 22:41 ` Thomas Kellerer <spam_eater@gmx.net>
2009-07-01 00:58 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2009-07-01 02:09 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2009-07-01 16:36 ` Rob Sargent <robjsargent@gmail.com>
2009-07-01 17:02 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2009-07-01 17:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-07-01 17:47 ` Tena Sakai <tsakai@gallo.ucsf.edu>
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