Re: field must appear in the GROUP BY clause or be used

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> Bill Moran wrote:
>> SELECT GCP.id,
>> GCP.Name
>> FROM Gov_Capital_Project GCP,
>> WHERE TLM.TLI_ID = $2
>> group by GCP.id
>> ORDER BY gcp.name;
>>
>> ERROR: column "gcp.name" must appear in the GROUP BY clause or be used
>> in an aggregate function
>>
>> This isn't my query, I'm translating a system prototyped in MSSQL to
>> Postgres. This query _does_ work in MSSQL. Does that constitute a
>> bug in MSSQL, or a shortcomming of Postgres, or just a difference of
>> interpretation?

> If MSSQL picks an arbitrary value for the non-group by attribute, it
> is violating spec.

They might be operating per spec. If "id" is a primary or unique key
for the table, then SQL99 (but not SQL92) says that it's sufficient to
group by the id column; the database is supposed to realize that the
other columns can't have more than one value per group, and allow direct
references to them. Or at least that's my interpretation of the pages
and pages in SQL99 about functional dependency. It seems like a pretty
useless frammish ... if you know that id is unique, why are you
bothering with GROUP BY at all?

Anyway, Postgres currently implements the SQL92 definition, which is
that you can't refer to an ungrouped column except within an aggregate
function call. So you need to call out all the columns to be referenced
in GROUP BY.

regards, tom lane