PostgreSQL多维数组 [英] PostgreSQL multidimensional arrays
问题描述
我试图以多维数组的形式传递数据，并且我的行为对我来说似乎很奇怪.具体来说，我正在尝试从二维数组中提取一个元素(因此从我的二维数组中获取一个一维数组)，并且它无法按我期望的方式工作.
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.
在以下示例#2、4和&中，5可以按照我期望的方式工作，但1和3不要.
In the following examples #2, 4, & 5 work the way I'd expect, but 1 & 3 do not.
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)
是否有相关文档?我现在有一些对我来说足够好用的东西，但是它很丑，我担心它不会做接下来我想做的事情.从技术上讲，我得到一个2维数组，其中1维只有1个元素.我宁愿只得到一个数组.
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.
我读过(其中):
- 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.
推荐答案
Postgres数组 elements 始终是基本元素，即 scalar > 值.子数组在Postgres中不是元素".数组切片保留原始尺寸.
Postgres array elements are always base elements, i.e. scalar values. Sub-arrays are not "elements" in Postgres. Array slices retain original dimensions.
您可以提取基本元素，它是标量元素数据类型的值.
或者，您可以提取数组切片，该数组保留原始数组数据类型和原始数组维.
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.
您将子数组检索为元素"的想法与此矛盾，只是没有实现.
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:
如果将任何维度写为切片，即包含冒号，则所有尺寸均视为切片.任何尺寸只有一个单个数字(无冒号)被视为从1到数字指定的.例如，
[2]被视为[1:2]...
您的第一个示例尝试引用未找到的基本元素(2-D数组中需要两个数组索引).因此Postgres返回NULL.
您的第三个示例只是将结果NULL包装在新数组中.
Your 1st example tries 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 example just wraps the resulting NULL in a new array.
To flatten an array slice (make it a 1-D array) you can unnest() and feed the resulting set to a new ARRAY constructor. Either in a correlated subquery or in a LATERAL join (requires pg 9.3+). Demonstrating both:
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;
并请务必阅读手册的当前版本.您的参考文献指向的是Postgres 9.1，但是您实际上是在使用Postgres 9.4.
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.
相关:
这篇关于PostgreSQL多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
