Re: Strange primary key constraint influence to grouping

Gra*vydas Valeika wrote:

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."

I completely agree with documentation.

But my case shows that "not valid" expression which refers to
column which is ungrouped still works in 9.1.

It is not an invalid expression in the SELECT list, because it is
functionally dependent on the primary key -- that is, given a
particular primary key, there is only one value the expression can
have. Because of this, adding the expression to the GROUP BY list
cannot change the set of rows returned by the query. It is pointless
to include the expression in the GROUP BY clause, so it is not
required. This allows faster query execution.

This is a new feature, not a bug.

-Kevin