Menu

string_agg и array_agg в PostgreSQL (аналог GROUP_CONCAT)

string_agg склеивает значения группы в одну строку, а array_agg собирает их в массив. Порядок, DISTINCT, FILTER, обработка NULL и частые ошибки.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

string_agg(expression, separator) склеивает значения каждой группы в одну строку, а array_agg(expression) собирает их в массив. Чтобы управлять порядком, поставьте ORDER BY внутрь вызова: string_agg(name, ', ' ORDER BY name).

Для IT получится Bo, Kim, Li и {6100,4800,3900}, для Sales Ana, Raj и {5000,4200}. Обе функции обычные агрегатные, как count или sum: с GROUP BY они работают по группам, без него по всей таблице.

Порядок значений

Без ORDER BY внутри агрегата значения идут в том порядке, в каком приходят строки, и он может меняться от запуска к запуску. ORDER BY в конце запроса сортирует строки результата, а не значения внутри каждой строки. Внутренний ORDER BY пишется после последнего аргумента, без запятой:

string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)

Удаление дубликатов через DISTINCT

Ada купила ручку дважды, но DISTINCT выводит её один раз: book, pen. С DISTINCT в ORDER BY можно использовать только агрегируемое значение. Сортировка по другому столбцу падает с ошибкой «in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list».

Как ведут себя NULL и пустые группы

string_agg пропускает значения NULL и возвращает Bo, Li. array_agg их сохраняет: {Bo,Li,NULL}. Чтобы убрать NULL из array_agg, добавьте FILTER (WHERE name IS NOT NULL).

На нуле строк обе функции возвращают NULL, а не пустую строку или пустой массив. Оборачивайте их, если вызывающий код ждёт значение: COALESCE(string_agg(...), '') или COALESCE(array_agg(...), '{}').

Агрегация только части строк через FILTER

FILTER (WHERE ...) ограничивает одну агрегатную функцию подходящими строками, а остальная часть запроса видит все строки:

В IT 3 человека, а в списке Bo, Li; в Sales 2 человека, а в списке Ana.

Список для каждой строки другой таблицы

Чтобы показать каждого клиента со списком его товаров, агрегируйте в подзапросе, тогда внешнему запросу не нужен GROUP BY:

SELECT c.id, c.name, c.email,
       (SELECT string_agg(o.product, ', ' ORDER BY o.product)
        FROM orders o
        WHERE o.customer_id = c.id) AS products
FROM customers c;

Клиенты без заказов остаются в результате, а products у них NULL. Соединение с GROUP BY сохранит их, только если это LEFT JOIN.

Частые ошибки и варианты

  • Числа: string_agg принимает текст, поэтому string_agg(id, ',') падает с ошибкой «function string_agg(integer, unknown) does not exist». Приведите тип: string_agg(id::text, ',').
  • Первые N в группе: возьмите срез упорядоченного массива, (array_agg(name ORDER BY salary DESC))[1:2].
  • Результат в JSON: json_agg и jsonb_agg собирают строки в массив JSON, а jsonb_object_agg(key, value) строит объект; см. JSONB.
  • Массив уже есть: array_to_string(arr, ', ') склеивает существующий массив, а unnest превращает его обратно в строки (массивы).
  • Оконная версия: обе функции работают и как оконные, например string_agg(name, ', ') OVER (PARTITION BY dept), чтобы показать весь список в каждой строке.

Часто задаваемые вопросы

Какой аналог GROUP_CONCAT в PostgreSQL?

string_agg(column, ','). В отличие от GROUP_CONCAT в MySQL, разделитель обязателен, а порядок указывается внутри вызова: string_agg(name, ', ' ORDER BY name).

Как использовать string_agg с числами?

Приведите значение к тексту: string_agg(id::text, ','). string_agg принимает только текст (или bytea), поэтому с целочисленным аргументом падает с ошибкой «function string_agg(integer, unknown) does not exist».

Есть ли у string_agg ограничение длины?

Отдельного ограничения нет; результат это обычное значение text, которое вмещает до 1 ГБ. GROUP_CONCAT в MySQL обрезает результат по group_concat_max_len, а PostgreSQL ничего не обрезает.

Как получить первые N значений через array_agg?

Отсортируйте внутри агрегата и возьмите срез результата: (array_agg(name ORDER BY score DESC))[1:3] вернёт три первых имени в каждой группе.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать