2

It seems postgres supports an array-append natively with an operator:

||  array-to-array concatenation    ARRAY[1,2,3] || ARRAY[4,5,6]    {1,2,3,4,5,6}

Source: https://www.postgresql.org/docs/current/functions-array.html.

Does it support vector/element-wise arithmetic operations natively (i.e., without writing a new function for it?)

For example, something like:

[1,2,3] + [1,1,1] = [2,3,4]
[1,2,3] - [1,1,1] = [0,1,2]
[1,2,3] * [2,2,2] = [2,4,6]
[1,2,3] / [2,2,2] = [.5, 1, 1.5]

Related question is here: Vector (array) addition in Postgres, though it's about 6 years old so perhaps there have been changes in postgres since then?

David542
  • 104,438
  • 178
  • 489
  • 842

2 Answers2

2

Postgres does not have a concept of "number arrays" that support such arithmetic. However, it is easy enough to implement with the array primitives. For example:

select t.*,
       (select array_agg(e.el1 * e.el2)
        from unnest(t.ar1, t.ar2) e(el1, el2)
       ) ar
from (select array[1,2,3] as ar1,  array[4,5,6] as ar2) t;

The unnest() "unzips" the two arrays in element order. The array agg then "rezips" them based on the mathematical operation you want.

Actually, Postgres does preserve the ordering of the array with the subquery. But if you want to be explicit, use with ordinality:

select t.*,
       (select array_agg(e.el1 * e.el2 order by n)
        from unnest(t.ar1, t.ar2) with ordinality e(el1, el2, n)
       ) ar
from (select array[1, 2, 3] as ar1,  array[4, 5, 6] as ar2) t
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If you install the intarray module, you can get some benefits of some new functions and operators, but nothing about arithmetic operations.

Edouard
  • 6,577
  • 1
  • 9
  • 20