I am new to postgres arrays.

I need to modify the first 4 elements of C arrays

The formulas is C[1:4] = A[1:4] B[1:4] This calculation is done over group_name dimension

Is there an elegant way of doing this, maybe without joins?

insert into the_table values
(1,'group 1', 'A', '{1,2,3,4,5}'), 
(2,'group 1', 'B', '{10,20,30,40,50}'),
(3,'group 2', 'A', '{-1,-2,-3,-4,-5}'), 
(4,'group 2', 'B', '{-10,-20,-30,-40,-50}'),
(5,'group 1', 'C', '{0,0,0,0,0}'), 
(6,'group 2', 'C', '{0,0,0,0,0}');

Input data:

Output data:

CodePudding user response：

You need a function that sums arrays on a given basis.

create or replace function sum_arrays(arr int[])
returns int[] language sql immutable as $$
    select array[
        arr[1][1]  arr[2][1],
        arr[1][2]  arr[2][2],
        arr[1][3]  arr[2][3],
        arr[1][4]  arr[2][4],
        0]
$$;

With this function the update looks quite elegant:

update the_table t
set val = sum_arrays
from (
    select group_name, sum_arrays(array_agg(val))
    from the_table
    where var in ('A', 'B')
    group by group_name
) s
where t.group_name = s.group_name
and t.var = 'C';

Test it in db<>fiddle.

CodePudding user response：

There are no piecewise operations on arrays in Postgres. You won't get around a few joins that unnest the arrays, join by index, and aggregate them back:

UPDATE the_table tab_c
SET val = (
  SELECT array_agg(a   b ORDER BY a_idx)
  FROM the_table tab_a,
  unnest(tab_a.val) WITH ORDINALITY AS val_a(a, a_idx),
  the_table tab_b,
  unnest(tab_b.val) WITH ORDINALITY AS val_b(b, b_idx)
  WHERE tab_a.group_name = tab_c.group_name
    AND tab_a.var = 'A'
    AND tab_b.group_name = tab_c.group_name
    AND tab_b.var = 'B'
    AND a_idx = b_idx
)
WHERE tab_c.var = 'C';
TABLE the_table;

^{(online demo)}