PostgreSQL - summing arrays by index

Question

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array columns by index and produces a new 48 array index as explained below

A = double[48] = {3,2,0,3....1}
B = double[48] = {1,0,3,2....5}
RESULT = double[48] = {A[0] + B[0], A[1] + B[1],...,A[47] + B[47]}

Thank you!

Answer 1

I would use unnest together with array_agg, like this: SQL Fiddle

PostgreSQL 9.3.1 Schema Setup:

create table t (
A double precision[5],
B double precision[5]);
insert into t values
('{3,2,0,3,1}', '{1,0,3,2,5}');

Query 1:

with c as(
select unnest(a) a, unnest(b) b from t)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c

Results :

| A | B | C |
|-----------|-----------|-----------|
| 3,2,0,3,1 | 1,0,3,2,5 | 4,2,3,5,6 |

As commented below, the query above will work well for the arrays of the same size. Otherwise it may produce an unexpected result.

If you need to support the arrays of different size, use this query:

with a as(
 select unnest(a) a from t),
b as(
 select unnest(b) b from t),
ar as(
 select a, row_number() over() r from a),
br as(
 select b, row_number() over() r from b),
c as(
 select ar.a, br.b from ar inner join br on ar.r = br.r)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c;

• Craig Ringer: That'll work well so long as the arrays are always the same length. If they differ it'll produce insane and bizarre results thanks to PostgreSQL's quirky handling of set-returning functions in the SELECT list.

Answer 2

Using @cha's schema, here's how to do it in PostgreSQL 9.4 using support for unnest ... with ordinality:

SELECT array_agg(unnest_a.unnest_a + unnest_b.unnest_b ORDER BY unnest_a.ordinality)
FROM t,
 LATERAL unnest(a) WITH ORDINALITY AS unnest_a 
 INNER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b 
 ON (unnest_a.ordinality = unnest_b.ordinality);

Or, to handle arrays of uneven length using a FULL OUTER JOIN:

SELECT array_agg(
 coalesce(unnest_a.unnest_a,0) + 
 coalece(unnest_b.unnest_b,0) 
 ORDER BY unnest_a.ordinality
 )
FROM t,
 LATERAL unnest(a) WITH ORDINALITY AS unnest_a 
 FULL OUTER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b 
 ON (unnest_a.ordinality = unnest_b.ordinality);

For PostgreSQL 9.3, I'd just use PL/Python:

create or replace function sumarrays(a float8[], b float8[]) returns float8[] language plpythonu as $$
return [ (ax or 0) + (bx or 0) for (ax, bx) in map(None, a, b) ]
$$;

To support 9.3 without using extension procedure languages you can replace each LATERAL unnest ... WITH ORDINALITY with a subquery using row_number(), e.g.

 LATERAL unnest(a) WITH ORDINALITY AS unnest_a 

becomes:

 (SELECT unnest_a, row_number() OVER () AS ordinality
 FROM unnest(a) AS unnest_a) AS unnest_a

Technically there's no gurarantee that PostgreSQL will process the unnested rows in order, so row_number() OVER () is kind of risky, but in practice it's fine with all current PostgreSQL versions.

The best solution to this would really be to write a simple C extension, though.

• cha: Thanks for the hint about the ordinality. I was not aware about it. BTW, the same thing can be achieved using a ROW_NUMBER()
• Craig Ringer: @cha I was adding it as you were writing that. Technically there's no gurarantee that PostgreSQL will process the unnested rows in order, so row_number() OVER () is kind of risky, but in practice it's fine with all current PostgreSQL versions.
• cha: I have added my version as well

Answer 3

Another way of doing it is by using a plpgsql function:

create or replace function sum_int_arrays(a int[], b int[]) returns int[] as $$
declare
 c int[];
begin
 for i in 1..array_length(a, 1) loop
 c[i] := a[i] + b[i];
 end loop;
 return c;
end
$$ language plpgsql immutable strict;

Usage:

select sum_int_arrays('{1,2,3}'::int[], '{4,5,6}'::int[]);
 {5,7,9}
select sum_int_arrays('{1,2,3}'::int[], '{4,5,6,7}'::int[]);
 {5,7,9}
select sum_int_arrays('{1,2,3,4,5}'::int[], '{4,5,6,7}'::int[]);
 {5,7,9,11,NULL}
select sum_int_arrays(null, '{4,5,6,7}'::int[]);
 (null)