Postgres: on conflict, summing two vectrors(arrays)-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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]