CODEWITHSUNDEEP
sqlarrayspostgresql
Simonas
Simonas

Reputation: 303

Array to columns in PostgreSQL when array length is non static

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?

Upvotes: 1

Views: 2580

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

Related Questions