Reshape array in Postgres?

Question

I have a 1-dimensional array of values in Postgres, such as:

[ "1", "4", "2", "5", "3", "6" ]

Is there a way to change this to a 2-D array?

[ ["1", "4"], ["2", "5"], ["3", "6"] ]

Now the dimensions are 3x2 instead of 1x6.

@GMB Guaranteed not to be. Really what I'm trying to do is the last step of a transpose (it's a CodeWars problem where I can't see the solution). Since I wrote this I actually figured out how to get the transpose to work bypassing this particular step, but I'm still curious if there is a good way to do reshape essentially. — Evan Zamir
– Evan Zamir, Commented Feb 6, 2020 at 0:15

klin · Accepted Answer · 2020-03-17 23:07:47Z

1

Use two aggregates:

select array_agg(arr order by ord)
from (
    select (ord+ 1) / 2 as ord, array_agg(arr) as arr
    from unnest(array[1,4,2,5,3,6]) with ordinality as u(arr, ord)
    group by 1
    ) s

or an array constructor:

select array(
    select array_agg(unnest)
    from unnest(array[1,4,2,5,3,6]) with ordinality
    group by (ordinality+ 1) / 2
    order by (ordinality+ 1) / 2
    )

Db<>fiddle.

edited Mar 17, 2020 at 23:07

answered Feb 6, 2020 at 0:22

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Reshape array in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related