2

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!

5

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;
  • 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. – Craig Ringer Sep 30 '14 at 1:35
3

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 '14 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. – Craig Ringer Sep 30 '14 at 1:45
  • I have added my version as well – cha Sep 30 '14 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.