Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 084B79FB536 for ; Thu, 21 Jun 2007 10:09:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 70802-09 for ; Thu, 21 Jun 2007 10:08:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id 5D6DF9FB7F7 for ; Thu, 21 Jun 2007 10:08:51 -0300 (ADT) Received: by fafnir.androme.com (Postfix, from userid 1003) id E2306B99130; Thu, 21 Jun 2007 15:08:49 +0200 (CEST) Message-ID: <467A7861.3050106@androme.es> Date: Thu, 21 Jun 2007 15:08:49 +0200 From: =?ISO-8859-1?Q?Dani_Casta=F1os?= User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Results per letter query References: <467A406A.3060907@androme.es> <20070621100425.GH1405@a-kretschmer.de> <467A562C.2020504@androme.es> <20070621105045.GJ1405@a-kretschmer.de> <467A59F9.4030801@androme.es> <20070621111206.GK1405@a-kretschmer.de> In-Reply-To: <20070621111206.GK1405@a-kretschmer.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200706/163 X-Sequence-Number: 28653 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!