postgresql: Aggregating arrays of text

Question

I want to get an array of all words associated with each id in this example data:

id | words
---|------------------
1  | {foo,bar,zap,bing}
2  | {foo}
1  | {bar,zap}
2  | {bing}
1  | {bing}
2  | {foo,bar}

Outputs:

id | allwords
---|--------------------------------
1  | {foo,bar,zap,bing,bar,zap,bing}
2  | {foo,bing,foo,bar}

I tried using array_agg(words) but it yields:

ERROR: could not find array type for data type text[]

What's the proper approach here? I want all words, even the duplicates.

score 6 · Accepted Answer · edited May 23 '17 at 10:25

array_agg collects the results of a column into an array; it doesn't aggregate arrays.

array_cat is the functionality you want, but it isn't an aggregate function.

To define your own aggregate based on it, use this code:

CREATE AGGREGATE array_cat_aggregate (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

which I blatantly copied from this answer: https://stackoverflow.com/a/11763245/1394393. (I don't think the question is a duplicate.)

Pretending the table is called temp, then you can select using the function with a GROUP BY:

SELECT id, array_cat_aggregate(words)
FROM temp
GROUP BY id;

score 2 · Answer 2 · answered May 22 '13 at 03:02

2

As an alternative to jpmc26 solution you can also combine array_agg with a subquery that unnest your arrays:

SELECT id, array_agg(w) AS allwords
FROM
(
  SELECT
   id,
   unnest(words) AS w
  FROM words_table
) x
GROUP BY id;

answered May 22 '13 at 03:02

Anthony Accioly

I would like to note that this answer extends to reducing the array to unique values better than mine. One need only add `DISTINCT` like so: `array_agg(DISTINCT w)`. – jpmc26 May 22 '13 at 03:57

2 Answers2