From: Peter Eisentraut <peter_e@gmx.net>
To: Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using count on a join, group by required?
Date: Wed, 11 Aug 2010 15:39:28 +0300
Message-ID: <1281530368.8340.4.camel@fsopti579.F-Secure.com> (raw)
In-Reply-To: <AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com>
References: <AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com>
On tis, 2010-08-10 at 22:21 -0430, Jose Ildefonso Camargo Tolosa wrote:
> And it works, it gives me something like:
>
> product_id | name | code | manufacturer_id |
> manufacturer_name | num_serials
> ------------+----------------------+---------------+-----------------+----------------------------+-------------
> 17 | THE product | 1235711131719 | 19 |
> THE product's manufacturer | 5
> 6 | Car Battery 500A 12V | 7591512021575 | 8 |
> Acumuladores Duncan, C.A. | 11
> 1 | Test product 1 | 123456789012 | 1 |
> Test Manufacturer | 6
>
> Which is correct, and exactly what I wanted.
>
> So far, so good. The thing is: the group by clause, I had to add it
> because the parser forced me to, because it complained like this:
>
> ERROR: column "manufacturer.name" must appear in the GROUP BY clause
> or be used in an aggregate function
>
> and I had to include *all* the requested columns on the group by
> clause, can anybody tell me why? or at least point to some doc that
> help me understanding this?
This is fixed in PostgreSQL 9.1devel (*); there you only need to put the
primary key into the GROUP BY clause. Earlier versions didn't know that
that was enough to ensure a deterministic result.
(*) -- It will probably be a bit over a year before that is released.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter_e@gmx.net, ildefonso.camargo@gmail.com
Subject: Re: Using count on a join, group by required?
In-Reply-To: <1281530368.8340.4.camel@fsopti579.F-Secure.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox