Skip site navigation (1) Skip section navigation (2)

Re: Results per letter query

From: Dani Castaños <dcastanos(at)androme(dot)es>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Results per letter query
Date: 2007-06-21 13:08:49
Message-ID: 467A7861.3050106@androme.es (view raw or flat)
Thread:
Lists: pgsql-sql

A. Kretschmer escribió:
> am  Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes:
>   
>>> change the generate_series(65,90) to generate_series(32,90)
>>>
>>>
>>> Andreas
>>>  
>>>       
>> With only changing 65 to 32:
>>
>> ERROR:  invalid regular expression: parentheses () not balanced
>>
>> I think, it could be a problem with UPPER and non alphabetical chars
>>     
>
> No, the ~ - operator (Regex), try this:
>
> select chr(x), count(1) from generate_series(32,90) x, w where upper(substring (w.t from 1 for 1)) = chr(x) group by 1;
>
>
> Andreas
>   
Thanks Andreas!
I thik i've found a better solution for my problem:

I got another solution, but I've used EXPLAIN ANALYZE, and yours is better:

EXPLAIN ANALYZE select chr(x), count(1) from generate_series(32,90) x, 
sip_customer_services where upper(substring 
(sip_customer_services.service_name from 1 for 1)) = chr(x) group by 1;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=37.00..39.25 rows=150 width=4) (actual 
time=1.652..1.716 rows=13 loops=1)
   ->  Hash Join  (cost=9.38..36.25 rows=150 width=4) (actual 
time=0.979..1.490 rows=18 loops=1)
         Hash Cond: (chr("outer".x) = 
upper("substring"(("inner".service_name)::text, 1, 1)))
         ->  Function Scan on generate_series x  (cost=0.00..12.50 
rows=1000 width=4) (actual time=0.114..0.332 rows=59 loops=1)
         ->  Hash  (cost=9.30..9.30 rows=30 width=10) (actual 
time=0.647..0.647 rows=18 loops=1)
               ->  Seq Scan on sip_customer_services  (cost=0.00..9.30 
rows=30 width=10) (actual time=0.295..0.442 rows=18 loops=1)
 Total runtime: 2.147 ms
(7 rows)

test=# EXPLAIN ANALYZE select count(*), upper(substr(service_name, 1, 
1)) from sip_customer_services group by upper(substr(service_name, 1,1));
                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=9.60..10.13 rows=30 width=10) (actual 
time=0.704..0.766 rows=13 loops=1)
   ->  Seq Scan on sip_customer_services  (cost=0.00..9.45 rows=30 
width=10) (actual time=0.332..0.530 rows=18 loops=1)
 Total runtime: 1.065 ms
(3 rows);

Thank you very much, anyway!

In response to

pgsql-sql by date

Next:From: Richard HuxtonDate: 2007-06-21 13:18:37
Subject: Re: Select last there dates
Previous:From: Loredana CurugiuDate: 2007-06-21 13:00:05
Subject: Re: [SQL] Select last there dates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group