Почему нет "SELECT foo. *... GROUP BY foo.id" в Postgres?

Ответ 1

На всякий случай другие люди спотыкаются над этим вопросом:

Начиная с PostgreSQL 9.1 достаточно перечислить столбцы первичного ключа в предложении group by (так что пример из вопроса будет работать сейчас).

Ответ 2

Что именно у вас будет выход postgresql? Вы используете агрегатную функцию и пытаетесь вывести "что-то".

Ах. Я вижу, что вы можете сделать. Используйте подзапрос.

select foo.*, (select count(*) from bar where bar.foo_id=foo.id) from foo;

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

Да, в теории группирования по foo.id будет достаточно (т.е. ваш запрос плюс "группа by foo.id" ). Но, по-видимому (я его протестировал) postgresql этого не сделает. Другой вариант - "group by foo.id, foo.foo, foo.bar, foo.baz" и все остальное, что в "foo. *".

Другой способ, с которым Гуффа идет, заключается в следующем:

SELECT foo.*, COALESCE(sub.cnt, 0)
FROM foo
LEFT OUTER JOIN (
  SELECT foo_id, count(*) AS cnt
  FROM bar
  GROUP BY foo_id) sub
ON sub.foo_id = foo.id;

Это будут два запроса, хотя (один подзапрос, который запускается только один раз), что может иметь значение, но, вероятно, не будет. Если вы можете просто обойтись без "foo. *", Вы можете использовать вторую версию, которая явно группирует все столбцы.

Ответ 3

Некоторые базы данных более расслаблены по этому поводу, для хорошего и плохого. Запрос неспецифичен, поэтому результат в равной степени неспецифичен. Если база данных разрешает запрос, она возвращает одну запись из каждой группы, и ее не волнует какой из них. Другие базы данных более конкретны и требуют, чтобы указать, какое значение вы хотите от группы. Они не позволят вам написать запрос с неспецифическим результатом.

Единственными значениями, которые вы можете выбрать без агрегата, являются те, что указаны в предложении group by:

select foo.id, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

Вы можете использовать агрегаты для получения других значений:

select foo.id, min(foo.price), count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

Если вам нужны все значения из таблицы foo, вы можете поместить их все в предложение group by (если это дает правильный результат):

select foo.id, foo.price, foo.name, foo.address, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id, foo.price, foo.name, foo.address

Или вы можете присоединиться к таблице с подзапросом:

select foo.id, foo.price, foo.name, foo.address, sub.bar_count
from foo
inner join (
   select foo.id, bar_count = count(bar.id)
   from foo inner join bar on foo.id = bar.foo_id
   group by foo.id
) sub on sub.id = foo.id

Ответ 4

A GROUP BY требует, чтобы каждый столбец, возвращаемый запросом, был либо столбцом, содержащимся в инструкции GROUP BY, либо функцией агрегата (например, COUNT в вашем примере). Не видя, что такое предложение GROUP BY или что такое столбцы foo, трудно сказать, что именно происходит, но я думаю, проблема в том, что foo.* пытается вернуть один или несколько столбцов, которые не в вашем предложении GROUP BY.

Это действительно общее свойство SQL и не должно быть специфичным для PostgreSQL. Не знаю, почему это сработало для вас с SQLite или MySQL - возможно, все столбцы в foo.* на самом деле находятся в вашем предложении GROUP BY, но PostgreSQL не может понять это - попробуйте указать все столбцы foo явно.