# PostgreSQL - summing arrays by index

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 = {3,2,0,3....1}
B = double = {1,0,3,2....5}

RESULT = double = {A + B, A + B,...,A + B}


Thank you!

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

PostgreSQL 9.3.1 Schema Setup:

create table t (
A double precision,
B double precision);

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

|         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;

• 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. Sep 30, 2014 at 1:35

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.

• Thanks for the hint about the ordinality. I was not aware about it. BTW, the same thing can be achieved using a ROW_NUMBER()
– cha
Sep 30, 2014 at 1:43
• @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. Sep 30, 2014 at 1:45
• I have added my version as well
– cha
Sep 30, 2014 at 1:48