The total aggregate function of summing an array?

Question

I have a table with arrays as a single column, and I want to sum the elements of the array together:

> create table regres(a int[] not null); > insert into regres values ('{1,2,3}'), ('{9, 12, 13}'); > select * from regres; a ----------- {1,2,3} {9,12,13} 

I want the result to be:

 {10, 14, 16} 

that is: {1 + 9, 2 + 12, 3 + 13} .

Is there such a function elsewhere? The intagg extension looked like a good candidate, but such a function does not yet exist.

It is expected that arrays will contain from 24 to 31 elements, all elements are NOT NULL , and the arrays themselves will always be NOT NULL . All elements are basic int . There will be more than two rows for an aggregate. All arrays will have the same number of elements in the request. Different requests will have a different number of elements.

My implementation goal: PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu / Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Answer 1

A common solution in Postgres 9.3 + for any number of arrays with any number of elements.
Individual elements or the entire array can also be NULL:

 SELECT ARRAY ( SELECT sum(arr[rn]) FROM tbl t , generate_subscripts(t.arr, 1) AS rn GROUP BY rn ORDER BY rn ); 

This uses an implicit LATERAL JOIN (Postgres 9.3+).
With your values:

 SELECT ARRAY ( SELECT sum(arr[rn]) FROM ( VALUES ('{1,2,3}'::int[]) ,('{9,12,13}') ) t(arr) , generate_subscripts(t.arr, 1) AS rn GROUP BY rn ORDER BY rn ); 

Nontrivial example:

 SELECT ARRAY ( SELECT sum(arr[rn]) FROM ( VALUES ('{1,2,3}'::int[]) ,('{9,12,13}') ,('{1,1,1, 33}') ,('{NULL,NULL}') ,(NULL) ) t(arr) , generate_subscripts(t.arr, 1) AS rn GROUP BY rn ORDER BY rn ); 

Simplification in 9.4+ with WITH ORDINALITY

 SELECT ARRAY ( SELECT sum(elem) FROM tbl t , unnest(t.arr) WITH ORDINALITY x(elem, rn) GROUP BY rn ORDER BY rn ) 

Postgres 9.1

 SELECT ARRAY ( SELECT sum(arr[rn]) FROM ( SELECT arr, generate_subscripts(arr, 1) AS rn FROM tbl t ) sub GROUP BY rn ORDER BY rn ); 

The same thing works in later versions, but the set-return functions in the SELECT list SELECT not standard SQL and frown on someone. Therefore, use the above alternatives with current Postgres.

SQL Fiddle

Related answers with lots of explanation:

• PostgreSQL unsest () with item number
• Is there something like the zip () function in PostgreSQL that combines two arrays?