Postgres: on conflict, summing two vectrors(arrays)

Question

I'm trying to handle an array of counters column in Postgres for example, let's say I have this table

name	counters
Joe	[1,3,1,0]

and now I'm adding 2 values ("Ben", [1,3,1,0]) and ("Joe",[2,0,2,1])

I expect the query to sum between the 2 counters vectors on conflict ([1,3,1,0] + [2,0,2,1] = [3,3,3,1])

the expected result:

name	counters
Joe	[3,3,3,1]
Ben	[1,3,1,0]

I tried this query

insert into test (name, counters) 
values ("Joe",[2,0,2,1]) 
on conflict (name) 
do update set 
    counters = array_agg(unnest(test.counters) + unnest([2,0,2,1]))

but it didn't seem to work, what am I missing?

Is your array guaranteed to have four elements, and can I assume this is being done programmatically outside of a SQL editor? There is a way to do pairwise summations (https://stackoverflow.com/questions/24997131/pairwise-array-sum-aggregate-function), but if some assumptions can be made, the solution for this particular issue might be simpler. — Hambone, Apr 18 '22 at 12:30
Yes, the array is guaranteed to have a const amount of elements, and yes is being done programmatically. I already tried the suggested answer you sent but the main difference between the scenarios is that I'm trying to sum the current user counters vector with new counters vectors and in the other question they summing between current rows — Sato, Apr 18 '22 at 12:46

score 1 · Accepted Answer · answered Apr 18 '22 at 14:28

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.

score 0 · Answer 2 · answered Apr 18 '22 at 13:20

Based on your reply to my comments that it will always be four elements in the array and the update is being done by a program of some type, I would suggest something like this:

insert into test (name, counters)
values (:NAME, :COUNTERS)
on conflict (name) do
update set
  counters[1] = counters[1] + :COUNTERS[1],
  counters[2] = counters[2] + :COUNTERS[2],
  counters[3] = counters[3] + :COUNTERS[3],
  counters[4] = counters[4] + :COUNTERS[4]

Postgres: on conflict, summing two vectrors(arrays)

2 Answers2