Как объединить все целые массивы из всех записей в один массив в postgres

У меня есть столбец из массива типа integer. Как я могу объединить все их в один целочисленный массив?

For example: If I execute query: 

`select column_name from table_name`

I get result set as:

-[RECORD 1]----------
column_name | {1,2,3}
-[RECORD 2]----------
column_name | {4,5}

Как я могу получить {1,2,3,4,5} в качестве конечного результата?

Ответ 1

Вы можете использовать unnest, чтобы открыть массивы, а затем array_agg, чтобы поместить их назад вместе:

select array_agg(c)
from (
  select unnest(column_name)
  from table_name
) as dt(c);

Ответ 2

Определите тривиальный пользовательский агрегат:

CREATE AGGREGATE array_cat_agg(anyarray) (
  SFUNC=array_cat,
  STYPE=anyarray
);

и используйте его:

WITH v(a) AS ( VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6,7]))
SELECT array_cat_agg(a) FROM v;

Если вам нужен конкретный заказ, поместите его в общий вызов, т.е. array_cat_agg(a ORDER BY ...)

Это примерно O(n log n) для n строк (я думаю). Для лучшей производительности вам нужно написать его на C, где вы можете использовать более эффективный (но ужасный для использования) API C для массивов PostgreSQL, чтобы избежать повторного копирования массива на каждую итерацию.

Ответ 3

Единственный способ сделать это - внутри функции:

CREATE FUNCTION merge_arrays() RETURNS int[] AS $$
DECLARE
  this record;
  res  int[];
BEGIN
  FOR this IN
    SELECT column_name FROM table_name
  LOOP
    array_cat(res, this.column_name);
  END LOOP;
  RETURN res;
END; $$ LANGUAGE plpgsql;

Тогда вы можете

SELECT merge_arrays();

чтобы получить результат, который вы ищете.

Это, конечно, жестко кодирует ваше определение таблицы в функции, что может (или не обязательно) быть проблемой. Кроме того, вы можете поместить предложение WHERE в запросе цикла, чтобы ограничить записи, массивы которых вы хотите добавить; для этого вы можете использовать дополнительный параметр функции.

Имейте в виду, что у вас может получиться действительно большой массив, так как ваша таблица увеличивается по размеру и может повлиять на производительность. Вам действительно нужны все подмассивы из всех записей в одном большом массиве? Посмотрите на свое приложение и посмотрите, можете ли вы выполнить слияние на этом уровне, а не в одном запросе.