Объединение нескольких строк в массив с SQL на PostgreSQL

У меня есть таблица, построенная следующим образом:

oid | identifier | value
1   | 10         | 101
2   | 10         | 102
3   | 20         | 201
4   | 20         | 202
5   | 20         | 203

Я хотел бы запросить эту таблицу, чтобы получить такой результат:

identifier | values[]
10         | {101, 102}
20         | {201, 202, 203}

Я не могу понять, как это сделать. Это возможно? Как?

Большое спасибо.

Ответ 1

Это postgres, встроенный с нескольких версий, поэтому вам больше не нужно определять свои собственные, имя array_agg().

test=> select array_agg(n) from generate_series(1,10) n group by n%2;
  array_agg   
--------------
 {1,3,5,7,9}
 {2,4,6,8,10}

(это postgres 8.4.8).

Обратите внимание, что не указан ORDER BY, поэтому порядок строк результатов зависит от используемого метода группировки (здесь, хеш), то есть он не определен. Пример:

test=> select n%2, array_agg(n) from generate_series(1,10) n group by (n%2);
 ?column? |  array_agg   
----------+--------------
        1 | {1,3,5,7,9}
        0 | {2,4,6,8,10}

test=> select (n%2)::TEXT, array_agg(n) from generate_series(1,10) n group by (n%2)::TEXT;
 text |  array_agg   
------+--------------
 0    | {2,4,6,8,10}
 1    | {1,3,5,7,9}

Теперь я не знаю, почему вы получаете {10,2,4,6,8} и {9,7,3,1,5}, так как generate_series() должен отправлять строки в порядке.

Ответ 2

Вам нужно создать агрегированную функцию, например

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

затем

SELECT identifier, array_accum(value) AS values FROM table GROUP BY identifier;

НТН

Ответ 3

Вот код для запрошенного вывода.

select identifier, array_agg(value)
from (
  values
    (1   , 10         , 101),
    (2   , 10         , 102),
    (3   , 20         , 201),
    (4   , 20         , 202),
    (5   , 20         , 203)
  ) as tab (oid, identifier, value)
group by identifier
order by identifier;