How to split an array into columns

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