当数组长度为非静态时，数组到PostgreSQL中的列 [英] Array to columns in PostgreSQL when array length is non static

问题描述

我需要类似于 unnest()的东西，但是要取消嵌套到列而不是行.

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

我有一个包含id列和array列的表.如何取消嵌套到列的数组?具有相同ID的数组始终具有相同的数组长度.

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.

我正在寻找可与任何长度的数组一起使用的查询

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}

有人知道吗?

推荐答案

这不是逻辑吗?

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

SQL查询返回一组固定的列.您不能有一个常规查询有时返回两列，有时返回一或三.实际上，这就是使用数组的原因之一-它使您可以灵活地拥有可变数量的值.

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.

这篇关于当数组长度为非静态时，数组到PostgreSQL中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！