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.


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
    Jan 16 '18 at 9:42
  • @Noxville if yo uwant to make it faster, see the update ;) Jan 17 '18 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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