Cumulative array from previous rows array?

Question

Is there a query that would merge previous arrays into a cumulative array, that would result in:

id array_field   c_array
---------------------------------------------
1  {one,two}     {one,two}
2  {three}       {one,two,three}
3  {four,five}   {one,two,three,four,five}
4  {six}         {one,two,three,four,five,six}

I am happy you accepted my answer, but in this case answer provided by Erwin is much better from my point of view. Is there any reason why you accepted mine? — Tomas Greif, Mar 20 '14 at 07:47

score 2 · Accepted Answer · edited May 23 '17 at 12:29

It depends on what you work with. Seems like your base table holds text arrays text[].

You can use any aggregate function as window function. Per documentation:

In addition to these functions, any built-in or user-defined aggregate function can be used as a window function

There is array_agg() but it operates on scalar types, not on array types.
But you can create your own aggregate function easily.

To aggregate array types, create this aggregate function:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Details in this related answer:
Selecting data into a Postgres array

Now, the job is strikingly simple:

SELECT array_agg_mult(array_field) OVER (ORDER BY id) AS result_array
FROM   tbl

Since the aggregate is defined for polymorphic types, this works for any array type, not just text[].

SQL Fiddle including alternative solution for text representation in a list.

Tomas Greif · Answer 2 · 2014-03-19T09:15:29.670

You can use recursive CTE.

SQLFiddle

Data:

-- drop table if exists sample_data;
create table sample_data (id int, arr varchar[]);

insert into sample_data
  values
    (1,ARRAY['One','Two','Three']),
    (2,ARRAY['Four','Six']),    
    (3,ARRAY['Seven']);

Query:

with recursive cte as (
  select 
    id, arr, arr as merged_arr 
  from 
    sample_data
  where
    id = 1

  union all

  select 
    sd.id, sd.arr, cte.merged_arr || sd.arr
  from
    cte
    join sample_data sd on (cte.id + 1  = sd.id)
)

select * from cte

Result:

1;{One,Two,Three};{One,Two,Three}
2;{Four,Six};     {One,Two,Three,Four,Six}
3;{Seven};        {One,Two,Three,Four,Six,Seven}

score 0 · Answer 3 · answered Mar 19 '14 at 06:37

You need an UPDATE with a CTE that refers back to the previous id:

WITH prev AS (SELECT id, c_array AS prev_array FROM your_table)
UPDATE your_table
SET c_array = prev_array || array_field
FROM prev
WHERE id = prev.id + 1;

If you want to do this automatically, then you need a BEFORE INSERT trigger and in the trigger function you simply execute SELECT c_array || NEW.array_field INTO NEW.c_array FROM your_table WHERE id = NEW.id - 1 if - and only if - you set the id yourself. If the id comes from a sequence then you need an AFTER INSERT trigger with an update as described above.

Cumulative array from previous rows array?

3 Answers3