Обсуждение: How to split an array into columns
Say if I have an float8 array:
id| data
--|---------------
a | {1,2}
b | {2,4}
If I could using query to make it looks like this:
id| data[1] | data[2]
--|----------|-----------
a | 1 | 2
b | 2 | 4
Since I would have around 200,000 rows, I would prefer it having enough capacity to carry out the calculation such as sum().
Thank you so much!
a schrieb am 24.08.2018 um 11:01: > Say if I have an float8 array: > > id| data > --|--------------- > a | {1,2} > b | {2,4} > > If I could using query to make it looks like this: > > id| data[1] | data[2] > --|----------|----------- > a | 1 | 2 > b | 2 | 4 > > Since I would have around 200,000 rows, I would prefer it having > enough capacity to carry out the calculation such as sum(). Maybe I am missing something, but: select id, data[1], data[2] from the_table; will work just fine.
Thomas Kellerer <spam_eater@gmx.net> writes: > Maybe I am missing something, but: > select id, data[1], data[2] > from the_table; > will work just fine. If the arrays are of varying length, unnest() might be what the OP is looking for. regards, tom lane
On Fri, Aug 24, 2018 at 2:01 AM, a <372660931@qq.com> wrote: > > Say if I have an float8 array: > > id| data > --|--------------- > a | {1,2} > b | {2,4} > > . . . > > Since I would have around 200,000 rows, I would prefer it having enough capacity to carry out the calculation such as sum(). Is this something that would help you?: https://github.com/pjungwir/aggs_for_vecs (also on pgxn: https://pgxn.org/dist/aggs_for_vecs) It would let you say `SELECT vec_to_sum(data)` and get `{3,6}`. Paul