Postgresql "столбец должен отображаться в предложении GROUP BY или использоваться в агрегатной функции" и уникальное поле

#sql #postgresql #foreign-keys #inner-join #aggregate-functions

#sql #postgresql #внешние ключи #внутреннее соединение #агрегатные функции

Вопрос:

Я знаю, что этот вопрос задавали много раз, но я не нахожу ответа на вопрос, почему я получаю это сообщение об ошибке с уникальным полем:

Вот мои 2 таблицы и индекс:

 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
   /* Other `inner join`s but not relevant for the question */
   GROUP BY posts.id;

дайте мне сообщение об ошибке column "users.email" must appear in the GROUP BY clause or be used in an aggregate function .

Но поле электронной почты уникально (если оно что-то меняет), и у сообщения может быть только один пользователь (то есть одно электронное письмо).

Я не понимаю, почему этот запрос недействителен, поскольку невозможно иметь несколько значений email per post .

1. Более важный вопрос заключается в том, почему вы используете GROUP BY в первую очередь, когда вы никогда не выбираете какие-либо агрегатные функции. Возможно, вы можете описать, что вы хотите, чтобы ваш запрос выполнял здесь.

2. Запрос был облегчен, чтобы сосредоточиться на части ошибки. Конечно, он делает больше вещей, чем это происходит на самом деле. Но я хочу понять, почему эта часть недействительна.

3. С содержанием данного сообщения может быть связано более одного пользовательского электронного письма. Имейте в виду, что даже если отношения действительно являются взаимно однозначными, Postgres об этом не знает.

4. Я не уверен, почему. users.id также уникально, поэтому я не могу придумать способ, при котором сообщение может иметь более одного пользователя. Я вижу эту ошибку в разных запросах уже несколько лет, поэтому я немного разочарован, что не понимаю ее правильно: D

5. В этом суть: даже если это может быть один к одному, Postgres этого не знает. Для справки, ваш запрос будет выполняться без ошибок (и с правильным ожидаемым набором результатов) в MySQL с ONLY_FULL_GROUP_BY выключенным режимом.

Ответ №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 довольно умен в отношении функциональных зависимостей, но не настолько умен. Он понимает концепцию функционально зависимых столбцов, но не в таблицах. Он не может предвидеть, что a post однозначно ссылается на пользователя, даже если у вас установлен правильный внешний ключ. Я не думаю, что такие вещи также определены в стандартном ANSI SQL.