sql - PostgreSQL group by 没有聚合函数。为什么它有效？

Question

有一个奇怪的行为。我知道在使用 GROUP BY 时，SELECT 中的所有列都应该在 group by 子句中，或者具有聚合函数。然而，下一个代码显示了一些有争议的东西:

// id is a primary key
select id, name from user group by name; <-- error because of group by (expected)
select id, name from user group by id; <-- works (unexpected!)

我错过了什么？找不到说明分组依据中主键的某些潜在特殊情况的文档。

Answer 1

这在 the docs 中有所介绍，但不是特别明显。 :

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when 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.

在这种情况下，我猜测 id 是表 user 的主键，这将使 name 在功能上依赖于 id.