Re: Strange primary key constraint influence to grouping

On 2012-01-19 00:25, Gražvydas Valeika wrote:
> In PG 9.0 this script complains that: column "aaa.something" must appear
> in the GROUP BY clause or be used in an aggregate function. Sorry, don't
> have 9.0 at my hands, but error message is similar to quoted.
> Same error is raised in 9.1 when ', constraint pk_aaa primary key (id)'
> is commented out.
>
> With PK constraint in place, this script runs happily, without complaints.

Hi,

This is because PostgreSQL 9.1 added the feature of simple checking of
functional dependencies for GROUP BY. The manual of 9.1 explains quite
well when PostgreSQL considers there to be a functional dependency.

"When GROUP BY is present, it is not valid for the SELECT list
expressions to refer to ungrouped columns except within aggregate
functions or if the ungrouped column is functionally dependent on the
grouped columns, since there would otherwise be more than one possible
value to return for an ungrouped column. A functional dependency exists
if the grouped columns (or a subset thereof) are the primary key of the
table containing the ungrouped column."

http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-GROUPBY

Best regards,
Andreas

--
Andreas Karlsson