Объединить значения массива при объединении/агрегации

У меня есть таблица со столбцом типа массива:

 title       tags
"ridealong";"{comedy,other}"
"ridealong";"{comedy,tragedy}"
"freddyjason";"{horror,silliness}"

Я хотел бы написать запрос, который создает единый массив для каждого заголовка (в идеальном мире это будет набор/дедуплицированный массив)

например.

select array_cat(tags),title from my_test group by title

Вышеприведенный запрос не работает, но я хотел бы создать 2 строки:

"ridealong";"{comedy,other,tragedy}"
"freddyjason";"{horror,silliness}"

Любая помощь или указатели будут очень оценены (Я использую Postgres 9.1)

Основываясь на помощи Крейга, я закончил со следующим (слегка измененный синтаксис, поскольку 9.1 жалуется на запрос точно так, как он показывает его)

SELECT t1.title, array_agg(DISTINCT tag.tag) 
FROM my_test t1, (select unnest(tags) as tag,title from my_test) as tag 
where tag.title=t1.title
GROUP BY t1.title;

Ответы

Ответ 1

Пользовательский агрегат

Подход 1: определение настраиваемой совокупности. Здесь я написал ранее.

CREATE TABLE my_test(title text, tags text[]);

INSERT INTO my_test(title, tags) VALUES
('ridealong', '{comedy,other}'),
('ridealong', '{comedy,tragedy}'),
('freddyjason', '{horror,silliness}');

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

select title, array_cat_agg(tags) from my_test group by title;

ПОСЛЕДОВАТЕЛЬНЫЙ запрос

... или поскольку вы не хотите сохранять порядок и хотите дедуплицировать, вы можете использовать запрос LATERAL, например:

SELECT title, array_agg(DISTINCT tag ORDER BY tag) 
FROM my_test, unnest(tags) tag 
GROUP BY title;

и в этом случае вам не нужен настраиваемый агрегат. Это, вероятно, довольно медленный бит для больших наборов данных из-за дедупликации. Однако удаление ORDER BY, если не требуется, может помочь.

Ответ 2

Очевидным решением будет LATERAL join (что также предлагает @CraigRinger), но это добавлено в PostgreSQL в 9.3.

В 9.1 вы не можете избежать подзапроса, но можете его упростить:

SELECT title, array_agg(DISTINCT tag)
FROM (SELECT title, unnest(tags) FROM my_test) AS t(title, tag)
GROUP BY title;

SQL Fiddle