PostgreSQL 多维数组

PostgreSQL multidimensional arrays

提问by user3742898

I'm trying to pass data around as a multidimensional array, and I'm getting behavior that seems odd to me. Specifically I'm trying to get a single element out of a 2 dimensional array (so a 1 dimensional array out of my 2 dimension array), and it doesn't work the way I'd expect.

我试图将数据作为多维数组传递，但我得到的行为对我来说似乎很奇怪。具体来说，我试图从二维数组中获取单个元素（因此从我的二维数组中获取一维数组），但它并没有像我期望的那样工作。

In the following examples #2, 4, & 5 work the way I'd expect, but 1 & 3 do not.

在以下示例中，#2、4 和 5 以我期望的方式工作，但 1 和 3 没有。

db=> select s.col[2] from (select array[[1,2,3],[4,5,6]] as col) s;
 col
-----

(1 row)

db=> select s.col[2:2] from (select array[[1,2,3],[4,5,6]] as col) s;
 col 
-----
 {{4,5,6}}
(1 row)

db=> select array[s.col[2]] from (select array[[1,2,3],[4,5,6]] as col) s;
 array  
--------
 {NULL}
(1 row)

db=> select array[s.col[2:2]] from (select array[[1,2,3],[4,5,6]] as col) s;
    array    
 -------------
 {{{4,5,6}}}
(1 row)

db=> select s.col[2][1] from (select array[[1,2,3],[4,5,6]] as col) s;
 col 
-----
   4
(1 row)

Is there doc on this? I have something that's working well enough for me right now, but it's ugly and I worry it won't do the things I want to do next. Technically I'm getting a 2 dimensional array, where 1 dimension only has 1 element. I'd rather just get an array.

有这方面的文档吗？我现在有一些对我来说效果很好的东西，但它很丑，我担心它不会做我接下来想做的事情。从技术上讲，我得到了一个二维数组，其中一维只有 1 个元素。我宁愿只得到一个数组。

I've read (among others):

我读过（除其他外）：

• http://www.postgresql.org/docs/9.1/static/arrays.html
• http://www.postgresql.org/docs/9.1/static/functions-array.html
• http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

• http://www.postgresql.org/docs/9.1/static/arrays.html
• http://www.postgresql.org/docs/9.1/static/functions-array.html
• http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

And I'm just not seeing what I'm looking for.

我只是没有看到我在寻找什么。

回答by Erwin Brandstetter

Postgres array elementsare always base elements, i.e. scalarvalues. Sub-arrays are not "elements" in Postgres. Array slices retain original dimensions.

Postgres 数组元素始终是基本元素，即标量值。子数组不是 Postgres 中的“元素”。数组切片保留原始尺寸。

You can either extract a base element, which is a value of the scalar element data type.
Or you can extract an array slice, which retains the original array data type and also original array dimensions.

您可以提取一个基本元素，它是标量元素数据类型的值。
或者您可以提取一个数组 slice，它保留原始数组数据类型以及原始数组维度。

Your idea to retrieve a sub-array as "element" would conflict with that and is just not implemented.

您将子数组检索为“元素”的想法会与此冲突，并且没有实现。

The manual might be clearer in its explanation.But at least we can find:

手册的解释可能更清楚。但至少我们可以发现：

If any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example, [2]is treated as [1:2]...

如果任何维度写为切片，即包含冒号，则所有维度都被视为切片。任何只有一个数字（没有冒号）的维度都被视为从 1 到指定的数字。例如，[2]被视为[1:2]...

Your 1st exampletries to reference a base element, which is not found (you'd need two array indexes in a 2-D array). So Postgres returns NULL.
Your 3rd examplejust wraps the resulting NULL in a new array.

您的第一个示例尝试引用未找到的基本元素（您需要二维数组中的两个数组索引）。所以 Postgres 返回 NULL。
您的第三个示例只是将结果 NULL 包装在一个新数组中。

To flattenan array slice (make it a 1-D array) you can unnest()and feed the resulting set to a new ARRAYconstructor. Either in a correlated subquery or in a LATERALjoin (requires pg 9.3+). Demonstrating both:

要展平数组切片（使其成为一维数组），您可以unnest()将结果集提供给新的ARRAY构造函数。在相关子查询或LATERAL连接中（需要 pg 9.3+）。证明两者：

SELECT s.col[2:2][2:3] AS slice_arr
     , x.lateral_arr
     , ARRAY(SELECT unnest(s.col[2:2][2:3])) AS corr_arr
FROM  (SELECT ARRAY[[1,2,3],[4,5,6]] AS col) s
     , LATERAL (SELECT ARRAY(SELECT * FROM unnest(s.col[2:2][2:3])) AS lateral_arr) x;

And be sure to read the current version of the manual. your references point to Postgres 9.1, but chances are you are actually using Postgres 9.4.

并且一定要阅读当前版本的手册。您的引用指向 Postgres 9.1，但您实际上使用的是 Postgres 9.4。

Related:

有关的：

• How to select 1d array from 2d array postgresql
• Unnest array by one level

• 如何从二维数组 postgresql 中选择一维数组
• 将数组取消一层