PostgreSQL - Aggregate column of type integer[] into type integer[]

Question

I have a table that has a column of type integer[]. I want to group by col1, sum col2, and merge the arrays in col3.

my_table
+-------------------------+
| col1 | col2 | col3      |
| ---- | ---- | --------- |
| qwer | 5    | {1,2,3,4} |
| asdf | 10   | {5,6,7,8} |
| qwer | 2    | {4,9,10}  |
+-------------------------+
SELECT col1, SUM(col2) AS sum, ???(col3) AS merge
FROM my_table
GROUP BY col1

This output would be acceptable:
=> 
+--------------------------------+
| col1 | sum | merge             | 
| ---- | --- | ----------------- |
| qwer | 7   | {1,2,3,4,4,9,10}  | 
| asdf | 10  | {5,6,7,8}         |
+--------------------------------+

The output would be ideal (the elements in the integer array are unique):
=> 
+------------------------------+
| col1 | sum | merge           | 
| ---- | --- | --------------- |
| qwer | 7   | {1,2,3,4,9,10}  | 
| asdf | 10  | {5,6,7,8}       |
+------------------------------+

For ??? I've tried using array_agg as well as converting the array to a string, using string_agg, and then converting back to an array. I couldn't get any of these solutions to work. Am I missing a trivial solution or functionality?

Ronaldinho · Accepted Answer · 2015-03-11 02:50:07Z

2

The PostgreSQL array_agg function does not accept array parameter. Here's your solution:

The SQL command:

SELECT
    xxx.col1,sum(xxx.col2),array_agg(xxx.one_col) 
FROM (select col1,col2,unnest(col3) as one_col from tmp2) AS xxx 
GROUP BY xxx.col1;

answered Mar 11, 2015 at 2:50

Ronaldinho

1352 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Aggregate column of type integer[] into type integer[]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related