Element wise array addition in PostgreSQL

Question

I have a table in PostgreSQL. I want to perform element-wise addition for the arrays(i.e. col) after grouping them by time. The arrays are of varying dimensions. Example as follows:

| time  | col               |
|------ |------------------ |
| 1     | {1,2}             |
| 1     | {3,4,5,6}         |
| 2     | {}                |
| 2     | {7}               |
| 2     | {8,9,10}          |
| 3     | {11,12,13,14,15}  |

The result should be as follows:

| time  | col               |
|------ |------------------ |
| 1     | {4,6,5,6}         |
| 2     | {15,9,10}         |
| 3     | {11,12,13,14,15}  |

I found this answer which does something similar for fixed dimension of 3 elements. But it does not work for varying dimensions.

DBFiddle for example.

This would be so much simpler with a properly normalized model. This is not the way arrays are meant to be used. — user330315
– user330315, Commented Jul 2, 2018 at 20:30
Normalize your data! Use a detail table to keep your variable sized data like arrays or look into mongodb — Jose Munoz
– Jose Munoz, Commented Jul 2, 2018 at 20:33
@a_horse_with_no_name What is the disadvantage of using arrays like this? I will read up more on normalizing the database. It will be helpful if you can provide me some good link for the same. — mrtyormaa
– mrtyormaa, Commented Jul 2, 2018 at 20:38
@DavidMunoz mongodb is not an option. I have to stick to PostgreSQL. I was under the impression that array operations are fast. If this is not true, I can use json objects or strings too. My final goal is to get a pair(s). Example, "0:300, 1:32, 3:0, 4:0, ..., 15:76 .." — mrtyormaa
– mrtyormaa, Commented Jul 2, 2018 at 20:41
To quote the manual: "Arrays are not sets" - everytime you find yourself manipulating individual elements of an array or searching for specific elements in an array you are doing something wrong. You should treat an array as a "atomic value". The same you treat a column firstname as a single value. And before you de-normalize even more and mis-use JSON, you might want to read this: blog.2ndquadrant.com/… — user330315
– user330315, Commented Jul 2, 2018 at 20:42

Lukasz Szozda · Accepted Answer · 2018-07-02 20:31:13Z

2

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} │
└──────┴──────────────────┘

answered Jul 2, 2018 at 20:31

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vérace Over a year ago

Why are the elements in the ARRAY_AGG not ordered?

Lukasz Szozda Over a year ago

@Vérace I am not sure if I follow you. They are ordered by its position not the value - dbfiddle.uk/…

Vérace Over a year ago

Maybe it's the SUM(elem) that confused me - why is it there? You're not adding the elements?

Vérace Over a year ago

And where did element 7 go?

Lukasz Szozda Over a year ago

@Vérace Elements are added by position, that was the whole point of the question. For instance for time =1 {1,2}, {3,4,5,6} = {1+3, 2+4, 5, 6}

|

Collectives™ on Stack Overflow

Element wise array addition in PostgreSQL

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related