Postgresql Generated Column requires sum of multiple integer array columns

Question

I have a table that has 2 integer columns and 2 integer array columns that keep scores in them. For example, my row would look like the follow:

{
physical_ed: 40,
music: 90,
first_term: {10,23,43},
second_term: {1,5,5,7}
}

The array fields are declared as an integer[].

I need to generate a score column that sums up all of these fields. So far I have tried:

ALTER TABLE scores DROP IF EXISTS score;
ALTER TABLE scores add COLUMN total_score integer GENERATED ALWAYS AS (physical_ed::integer + 
first_term[3]::integer + second_term[1]::integer + second_term[2]::integer + second_term[3]::integer) 
STORED;

The problem I have with the above, is it does not account for varying values in the array but sometimes that field could have 5 different values instead of just 3.

I have tried running a select statement and I can calculate the sum of each array in a select statement normally like so:

SELECT *, (SELECT SUM(s) FROM UNNEST(first_term) s) as total_first_term from scores;

Unfortunately, this does not work inside a generated column query and we do need it to be part of our generated total_score sum.

score 2 · Answer 1 · answered Jun 04 '20 at 21:18

First create a function the sums the elements of a array.

create or replace 
function array_sum (the_array integer[])
  returns integer
  language sql 
  immutable 
as $$
    select sum(a_element)::integer from (select unnest(the_array) a_element) u;
$$;

Then invoke that function to aid calculation your total_score column. (assuming your table already exists) then:

alter table scores 
      add column total_score integer 
      generated always as 
      (physical_ed + array_sum(first_term) + array_sum(second_term)) 
      stored;

Alternative you could concatenate the arrays before calling the function:

alter table scores 
      add column total_score integer 
      generated always as 
      (physical_ed + array_sum(first_term || second_term)) 
      stored;

I would tend to prefer the 1st, as I think it more clearly indicated my intent. But that is not a strong preference. Both generate the same result. See fiddle:

Postgresql Generated Column requires sum of multiple integer array columns

1 Answers1