PostgreSQL：“列必须出现在GROUP BY子句中或者被用于聚合函数” 和唯一字段

Question

CREATE TABLE posts (
    id bigint NOT NULL,
    user_id bigint NOT NULL,
    content text
);

CREATE TABLE users (
    id bigint NOT NULL,
    email character varying DEFAULT ''::character varying NOT NULL
)

CREATE UNIQUE INDEX index_users_on_email ON users USING btree (email);

以下是SQL请求：

SELECT posts.content, users.email /*, other aggregate fields not relevant for the question */
   FROM posts
   INNER JOIN users ON posts.user_id = users.id
   GROUP BY posts.id;

出现了错误 列"users.email"必须出现在GROUP BY子句中或用于聚合函数。

但是，如果更改了任何内容，电子邮件字段是唯一的，并且帖子只能有一个用户（因此只有一个电子邮件）。

既然不可能在post中有多个email值，为什么这个请求是无效的呢？

- Mathieu Mahé

---

更大的问题是，为什么您在第一次使用GROUP BY时从未选择任何聚合函数。也许您可以在此描述您想要查询执行的操作。 - Tim Biegeleisen

一个帖子的内容可能有多个与之关联的用户电子邮件。请记住，即使关系确实是一对一的，Postgres也不知道这一点。 - Tim Biegeleisen

我不确定原因。 users.id 也是独一无二的，所以我想不出一篇文章怎么会有多个用户。我现在已经看到这个错误好几年了，在不同的请求中都会出现，所以我有点沮丧，不理解它的正确性:D - Mathieu Mahé

这是重点：即使它可能是一对一，Postgres也不知道这一点。作为参考，如果在MySQL中关闭ONLY_FULL_GROUP_BY模式，则查询将无错误地运行（并且具有正确的预期结果集）。 - Tim Biegeleisen

Answer 1

你需要将用户表的主键添加到 group by 子句中，以使查询成为有效的聚合查询：

SELECT p.content, u.email /*, other aggregate fields not relevant for the question */
FROM posts p
INNER JOIN users u ON p.user_id = u.id
/* Other `inner join`s but not relevant for the question */
GROUP BY posts.id, u.id;

Postgres在函数依赖方面相当智能，但并不那么聪明。它理解功能相关的列的概念，但不跨表格预见，即使您设置了适当的外键，也不能预见post唯一地指向用户。我认为这种情况在标准ANSI SQL中也没有定义。

- GMB