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!
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
