X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 444CB11F6069 for ; Fri, 28 Apr 2006 13:36:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 54054-08 for ; Fri, 28 Apr 2006 13:36:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from bugs.unl.edu.ar (bugs.unl.edu.ar [168.96.132.208]) by postgresql.org (Postfix) with ESMTP id 6A9A311F6067 for ; Fri, 28 Apr 2006 13:36:32 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by bugs.unl.edu.ar (Postfix) with ESMTP id 66B24138BE; Fri, 28 Apr 2006 13:36:24 -0300 (ART) Received: from bugs.unl.edu.ar ([127.0.0.1]) by localhost (bugs.unl.edu.ar [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22467-06; Fri, 28 Apr 2006 13:36:22 -0300 (ART) Received: by bugs.unl.edu.ar (Postfix, from userid 33) id 97266138DE; Fri, 28 Apr 2006 13:36:22 -0300 (ART) To: Tom Lane Subject: Re: Slightly confused error message MIME-Version: 1.0 Date: Fri, 28 Apr 2006 13:36:19 -0300 From: Martin Marques Cc: Markus Schaber , PostgreSQL SQL List Organization: Cetul - UNL In-Reply-To: <1612.1146240424@sss.pgh.pa.us> References: <1612.1146240424@sss.pgh.pa.us> Message-ID: X-Sender: martin@bugs.unl.edu.ar User-Agent: RoundCube Webmail/0.1b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/209 X-Sequence-Number: 25018 On Fri, 28 Apr 2006 12:07:04 -0400, Tom Lane wrote: > Markus Schaber writes: >> I just stumbled over a slightly confused error message: > >> mydb=# select count(*),coverage_area from myschema.streets except select >> cd as coverage_area from countryref.disks group by > streets.coverage_area; >> ERROR: column "streets.coverage_area" must appear in the GROUP BY >> clause or be used in an aggregate function > >> As the query looks, streets.coverage_area is actually used in the GROUP > BY. > > The complaint is 100% accurate; the subquery that it's unhappy about is > > select count(*),coverage_area from myschema.streets > > which is an aggregating query, but coverage_area is being used outside > an aggregate without having been grouped by. I see lack of parenthesis in the sub-query: select count(*),coverage_area from myschema.streets except (select cd as coverage_area from countryref.disks) group by streets.coverage_area; -- --------------------------------------------------------- Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática | '@' || 'unl.edu.ar'; Universidad Nacional | DBA, Programador, del Litoral | Administrador ---------------------------------------------------------