PostgreSQL adding two integer Arrays-CodePudding

I have two instances of the type integer[] (generated by the Timescale histogram function), e.g. {3,5,1} and {2,2,2}.
I would like to add these two Arrays to {5,7,3} but using

SELECT "ID", histogram(...)   histogram(...)
FROM "ID"
GROUP BY "ID"

throws the following error: operator does not exist: integer[] integer[]. Is there any way to accomplish this?

CodePudding user response：

I don't think there is such a function.

In order to achieve your goal (in SQL) you'd have to unnest the arrays, then add the corresponding elements and aggregate the results back to array.

SELECT 
  array_agg(
    COALESCE(h1.val, 0) COALESCE(h2.val, 0) 
    ORDER BY COALESCE(h1.row_number, h2.row_number)
  ) as result
FROM 
  (SELECT ROW_NUMBER() over (), val FROM unnest('{3,5,1,5}'::int[]) as val) as h1
   FULL JOIN  (SELECT ROW_NUMBER() over (), val FROM unnest('{2,2,2}'::int[]) as val) as h2 ON h1.row_number=h2.row_number

I'm using ROW_NUMBER window function to get the array element number. FULL JOIN is required because the arrays may be of different length. It is also the reason why COALESCE is required when adding the elements.

Thanks to @a_horse_with_no_name the query may be rewritten using ordinality without relying on row_number() function:

SELECT 
  array_agg(
    COALESCE(h1.val, 0) COALESCE(h2.val, 0) 
    ORDER BY COALESCE(h1.no, h2.no)
  ) as result
FROM 
  unnest('{3,5,1,5}'::int[]) WITH ORDINALITY as h1(val, no)
  FULL JOIN unnest('{2,2,2}'::int[]) WITH ORDINALITY as h2(val, no) ON h1.no=h2.no