PostgreSQL: grouping arrays in a group by article

Question

We have a problem of grouping arrays into one array. We want to combine the values ​​from two columns into one array and combine these arrays from several rows.

Given the following input:

| id | name | col_1 | col_2 | | 1 | a | 1 | 2 | | 2 | a | 3 | 4 | | 4 | b | 7 | 8 | | 3 | b | 5 | 6 | 

We need the following output:

 | a | { 1, 2, 3, 4 } | | b | { 5, 6, 7, 8 } | 

The order of the elements is important and should correspond with the identifier of the aggregated rows.

We tried the array_agg function:

 SELECT array_agg(ARRAY[col_1, col_2]) FROM mytable GROUP BY name; 

Unfortunately, this statement causes an error:

 ERROR: could not find array type for data type character varying[] 

It seems impossible to combine arrays into group by using array_agg.

Any ideas?

Answer 1

UNION ALL

First you can “meet the turn” with UNION ALL :

 SELECT name, array_agg(c) AS c_arr FROM ( SELECT name, id, 1 AS rnk, col1 AS c FROM tbl UNION ALL SELECT name, id, 2, col2 FROM tbl ORDER BY name, id, rnk ) sub GROUP BY 1; 

Adapted to create an order of values ​​that you later requested. In the documentation:

The aggregate functions array_agg , json_agg , string_agg and xmlagg , as well as similar custom aggregate functions, produce significantly different result values ​​depending on the order of the input values. This ordering is not specified by default, but can be controlled by writing an ORDER BY in a generic call, as shown in section 4.2.7. Alternatively, supplying input values ​​from a sorted subquery will usually work.

Custom Aggregation Function

Or you can create a custom aggregate function as described in these related answers:
Selecting data in a Postgres array
Is there something like the zip () function in PostgreSQL that combines two arrays?

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

Then you can:

 SELECT name, array_agg_mult(ARRAY[col1, col2] ORDER BY id) AS c_arr FROM tbl GROUP BY 1 ORDER BY 1; 

Or, as a rule, faster, although not standard SQL:

 SELECT name, array_agg_mult(ARRAY[col1, col2]) AS c_arr FROM (SELECT * FROM tbl ORDER BY name, id) t GROUP BY 1; 

The added ORDER BY id (which can be added to such aggregate functions) guarantees the desired result:

 {1,2,3,4} {5,6,7,8} 

Or you might be interested in this alternative:

 SELECT name, array_agg_mult(ARRAY[ARRAY[col1, col2]] ORDER BY id) AS c_arr FROM tbl GROUP BY 1 ORDER BY 1; 

What produces 2-dimensional arrays:

 {{1,2},{3,4}} {{5,6},{7,8}} 

Answer 2

 select n, array_agg(c) as c from ( select n, unnest(array[c1, c2]) as c from t ) s group by n 

Or easier

 select n, array_agg(c1) || array_agg(c2) as c from t group by n 

---

To set a new order requirement:

 select n, array_agg(c order by id, o) as c from ( select id, n, unnest(array[c1, c2]) as c, unnest(array[1, 2]) as o from t ) s group by n