Array to columns in PostgreSQL when array length is non static

Question

I need something similar to unnest(), but for unnesting to columns rather than rows.

I have a table which has id column and array column. How can I unnest array to columns? Arrays with same ids always have same array length.

EDIT: I'm seeking for query which would work with any array lenght

SELECT ???? FROM table WHERE id=1;

id | array                array1 | array2 | ... | arrayn
---+----------            -------+--------+-----+-------
 1 | {1, 2, ..., 3}    ->   1    |   2    | ... |   3
 1 | {4, 5, ..., 6}         4    |   5    | ... |   6
 2 | {7, 8, ..., 9}

Anyone got idea?

select ar[1], ar[2] from (select string_to_array('a b c', ' ') ar) as sq; — Mani Deep
– Mani Deep, Commented Mar 21, 2019 at 11:01
Possible duplicate of PostgreSQL convert array returned from function to columns — Mani Deep
– Mani Deep, Commented Mar 21, 2019 at 11:05
Sorry, didn't mention that I want query to work with any array length — Simonas
– Simonas, Commented Mar 21, 2019 at 11:10
That's not possible. The number of columns of a query must be known before the query is executed. — user330315
– user330315, Commented Mar 21, 2019 at 11:26

Gordon Linoff · Accepted Answer · 2019-03-21 11:11:05Z

1

Wouldn't this be the logic?

select array[1] as array1, array[2] as array2
from t
where id = 1;

A SQL query returns a fixed set of columns. You cannot have a regular query that sometimes returns two columns and sometimes returns one or three. In fact, that is one reason to use arrays -- it gives you the flexibility to have variable numbers of values.

edited Mar 21, 2019 at 11:11

answered Mar 21, 2019 at 11:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Simonas Over a year ago

It works, but I'm seeking for query which works with any array length

Sergei Over a year ago

@Simonas have you found the solution?

Simonas Over a year ago

@Sergei, no, I haven’t. Instead I first query for array_upper and generate final query on application side.

Collectives™ on Stack Overflow

Array to columns in PostgreSQL when array length is non static

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related