In Postgresql 9.6, is there a clean way to multiply corresponding elements within two equally sized arrays?

For example

a = [1,2,9,4,5]
b = [5,8,4,0,12]

function(a, b) would return [5, 16, 36, 0, 60].

I'd prefer it as a function like this, since it'd be used in multiple places.


1 Answer 1


Parallel Unnest

What you want is something like this with parallel-unnest. Here we use the ARRAY() constructor rather than array_agg()

  SELECT a*b
  FROM unnest(
    ARRAY[1,2], -- ex1
    ARRAY[2,3]  -- ex2
  ) AS t(a,b)

Creating our own function

In functional programming, we call this zip, and you'd use that in a language with primitive functional support like Python, or the like. In languages with more advanced functional support, like Haskell, you'd use zipWith or Ramda.js R.zipWith()

This will return an array sized to the greater of ex1, or ex2. You can put this in a function like this,

CREATE FUNCTION array_zip_with_multiply( arr_q anyarray, arr_e anyarray )
RETURNS anyarray
AS $$
    SELECT a*b
    FROM unnest(
      arr_q, -- ex1
      arr_e  -- ex2
    ) AS t(a,b)

SELECT array_zip_with_multiply( ARRAY[1,2,9,4,5], ARRAY[5,8,4,0,12] );
(1 row)

Note: this won't work if there isn't an operator * defined for the type.

  • 1
    Excellent - exactly what I wanted. I'd have said zip-like, but wasn't sure if that'd dissuade some more cunning answers.
    – Noxville
    Commented Jan 16, 2018 at 9:42
  • @Noxville if yo uwant to make it faster, see the update ;) Commented Jan 17, 2018 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.