Tena Sakai | 1 Jul 02:55 2009
Picon

Re: it's not NULL, then what is it?

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 <at> gallo.ucsf.edu


-----Original Message-----
From: Rob Sargent [mailto:robjsargent <at> gmail.com]
Sent: Tue 6/30/2009 3:24 PM
To: Tena Sakai
Cc: pgsql-sql <at> 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 <at> 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?

Tena Sakai | 1 Jul 02:56 2009
Picon

Re: it's not NULL, then what is it?

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 <at> gallo.ucsf.edu


-----Original Message-----
From: pgsql-sql-owner <at> postgresql.org on behalf of Edward W. Rouse
Sent: Tue 6/30/2009 3:22 PM
To: pgsql-sql <at> 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 <at> postgresql.org [mailto:pgsql-sql-owner <at> postgresql.org]
On Behalf Of Tena Sakai
Sent: Tuesday, June 30, 2009 6:03 PM
To: pgsql-sql <at> 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 <at> gallo.ucsf.edu


Tena Sakai | 1 Jul 02:58 2009
Picon

Re: it's not NULL, then what is it?

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 <at> gallo.ucsf.edu


-----Original Message-----
From: Steve Crawford [mailto:scrawford <at> pinpointresearch.com]
Sent: Tue 6/30/2009 3:39 PM
To: Tena Sakai
Cc: pgsql-sql <at> 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 <at> [local]=> select * from barr;
LOG:  duration: 0.226 ms
 a | b
---+---
 a | b
 c | d
   | e
(3 rows)

steve <at> [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 <at> [local]=> select count(a) from barr;
LOG:  duration: 0.339 ms
 count
-------
     2
(1 row)

steve <at> [local]=> select count(*) from barr where a isnull;
LOG:  duration: 0.350 ms
 count
-------
     1
(1 row)



Tena Sakai | 1 Jul 04:09 2009
Picon

Re: it's not NULL, then what is it?

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 <at> gallo.ucsf.edu




-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo.kussama <at> 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 <at> 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

Tom Lane | 1 Jul 04:17 2009
Picon

Re: it's not NULL, then what is it?

"Tena Sakai" <tsakai <at> 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

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Tena Sakai | 1 Jul 05:40 2009
Picon

Re: it's not NULL, then what is it?

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 <at> gallo.ucsf.edu

-----Original Message-----
From: Tom Lane [mailto:tgl <at> sss.pgh.pa.us]
Sent: Tue 6/30/2009 7:17 PM
To: Tena Sakai
Cc: Edward W. Rouse; pgsql-sql <at> postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?

"Tena Sakai" <tsakai <at> 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

Rob Sargent | 1 Jul 18:36 2009
Picon

Re: 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 <at> gallo.ucsf.edu
>
>
>
>
> -----Original Message-----
> From: Osvaldo Kussama [mailto:osvaldo.kussama <at> 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 <at> 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
>

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Tamayo, Damian-p65828 | 1 Jul 18:40 2009

Xpath() return xml[]

Hi,
I have been developing in postgresql for a very short time. I am currently having problems that I believe can be resolved with the right query structure but as of yet have not been able to figure it out.

I am using xpath(xpathExpression, xml, Array[Array[]]) function form 8.3.7 (This is the version that I am currently implementing)

This function returns an xml[] … However, the problem is that in the xml array return I get the valid return values that I am looking for but I also get empty sets. How can I return only the sets that returned valid????

Example of output:

Id      |       xml[]
1       |       <Root/>
2       |   <Root/>
3       |  {}
4       | {} 
Etc…..

I want to only return the rows that did not return {}

Any help with this would be appreciated….

Thank you.

Damian Tamayo
General Dynamics C4 Systems

This email message is for the sole use of the intended recipient(s) and may contain GDC4S confidential or privileged information. Any unauthorized review, use, disclosure is prohibited. If you are not an intended recipient, please contact the sender by reply email and destroy all copies of the original message.

Tena Sakai | 1 Jul 19:02 2009
Picon

Re: it's not NULL, then what is it?

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 <at> gallo.ucsf.edu

-----Original Message-----
From: Rob Sargent [mailto:robjsargent <at> gmail.com]
Sent: Wed 7/1/2009 9:36 AM
To: Tena Sakai
Cc: pgsql-sql <at> 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 <at> gallo.ucsf.edu
>
>
>
>
> -----Original Message-----
> From: Osvaldo Kussama [mailto:osvaldo.kussama <at> 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 <at> 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
>


Tim Haak | 1 Jul 18:42 2009
Picon

Partitioned tables not using index for min and max 8.2.7?

Hi

I am running the following query again a partitioned table in 8.2.7. It does index scans which is unexpected as there are indexes for the log_date column.

min(log_date) from data.table

"Aggregate  (cost=739932.02..739932.02 rows=1 width=8)"
"  ->  Append  (cost=0.00..685106.21 rows=21930321 width=8)"
"        ->  Seq Scan on table  (cost=0.00..33827.10 rows=1215710 width=8)"
"        ->  Seq Scan on table_yy2009mm03 table  (cost=0.00..88056.39 rows=438839 width=8)"
"        ->  Seq Scan on table_yy2009mm04 table  (cost=0.00..204606.67 rows=7344967 width=8)"
"        ->  Seq Scan on table_yy2009mm05 table  (cost=0.00..159210.91 rows=5735091 width=8)"
"        ->  Seq Scan on table_yy2009mm06 table  (cost=0.00..199393.74 rows=7195574 width=8)"
"        ->  Seq Scan on table_yy2009mm07 table  (cost=0.00..11.40 rows=140 width=8)"

though if i run it only agains the one table it is significantly faster and uses the index

select min(log_date) from only data.table

"Result  (cost=0.06..0.07 rows=1 width=0)"
"  InitPlan"
"    ->  Limit  (cost=0.00..0.06 rows=1 width=8)"
"          ->  Index Scan using idx_table_log_date_only on table  (cost=0.00..68272.93 rows=1215710 width=8)"
"                Filter: (log_date IS NOT NULL)"

Am I doing something wrong or is this expected.

I tried the old method of
SELECT col FROM table ORDER BY col DESC LIMIT 1 But it does not work either.

-- Tim Haak Email: timh <at> lucidview.net Tel: +27 12 658 9019
Attachment (timh.vcf): text/x-vcard, 170 bytes

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Gmane