Vector (Array) Addition in Postgres

Vector arithmetic for arrays in postgres

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

Element wise array addition in PostgreSQL

You could use unnest with ordinality:

SELECT time, array_agg(elem order by nr)
FROM (SELECT time, nr, SUM(elem) AS elem
      FROM tab,unnest(col) WITH ORDINALITY a(elem, nr)
      GROUP BY time,nr) s
GROUP BY time;

DBFiddle Demo

Output:

┌──────┬──────────────────┐
│ time │    array_agg     │
├──────┼──────────────────┤
│    1 │ {4,6,5,6}        │
│    2 │ {15,9,10}        │
│    3 │ {11,12,13,14,15} │
└──────┴──────────────────┘

Postgres: on conflict, summing two vectrors(arrays)

There are two problems with the expression:

array_agg(unnest(test.counters) + unnest([2,0,2,1]))

• there is no + operator for arrays,
• you cannot use set-valued expressions as an argument in an aggregate function.

You need to unnest both arrays in a single unnest() call placed in the from clause:

insert into test (name, counters) 
values ('Joe', array[2,0,2,1]) 
on conflict (name) do 
update set 
counters = (
    select array_agg(e1 + e2)
    from unnest(test.counters, excluded.counters) as u(e1, e2)
    )

Also pay attention to the correct data syntax in values and the use of a special record excluded (find the relevant information in the documentation.)

Test it in db<>fiddle.

PostgreSQL adding two integer Arrays

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

Adding value to Postgres integer array

Use array_append function to append an element at the end of an array:

UPDATE table1
SET integer_array = array_append(integer_array, 5);

5 is a value of choice, it's of an integer datatype in your case. You probably need some WHERE clause as well not to update the entire table.

Try below to see how it works:

SELECT ARRAY[1,2], array_append(ARRAY[1,2],3);

Result:

 array | array_append
-------+--------------
 {1,2} | {1,2,3}

Select sum of an array column in PostgreSQL

SELECT id, (SELECT SUM(s) FROM UNNEST(monthly_usage) s) as total_usage from users;

Apply function to every element of an array in a SELECT statement

you can use internal "undocumented" function pg_catalog.pg_get_function_arguments(p.oid).

postgres=# SELECT pg_catalog.pg_get_function_arguments('fufu'::regproc);
 pg_get_function_arguments 
---------------------------
 a integer, b integer
(1 row)

Now, there are no build "map" function. So unnest, array_agg is only one possible. You can simplify life with own custom function:

CREATE OR REPLACE FUNCTION format_types(oid[])
RETURNS text[] AS $$
   SELECT ARRAY(SELECT format_type(unnest($1), null))
$$ LANGUAGE sql IMMUTABLE;

and result

postgres=# SELECT format_types('{21,22,23}');
          format_types          
-------------------------------
{smallint,int2vector,integer}
(1 row)

Then your query should to be:

SELECT proname, format_types(proallargtypes)
   FROM pg_proc
  WHERE pronamespace = 2200 AND proallargtypes;

But result will not be expected probably, because proallargtypes field is not empty only when OUT parameters are used. It is empty usually. You should to look to proargtypes field, but it is a oidvector type - so you should to transform to oid[] first.

postgres=# SELECT proname, format_types(string_to_array(proargtypes::text,' ')::oid[])
              FROM pg_proc
             WHERE pronamespace = 2200
             LIMIT 10;
           proname            |                    format_types                    
------------------------------+----------------------------------------------------
 quantile_append_double       | {internal,"double precision","double precision"}
 quantile_append_double_array | {internal,"double precision","double precision[]"}
 quantile_double              | {internal}
 quantile_double_array        | {internal}
 quantile                     | {"double precision","double precision"}
 quantile                     | {"double precision","double precision[]"}
 quantile_cont_double         | {internal}
 quantile_cont_double_array   | {internal}
 quantile_cont                | {"double precision","double precision"}
 quantile_cont                | {"double precision","double precision[]"}
(10 rows)

---

Related Topics