SQL PostgreSQL 中的数组数组

Array of arrays in PostgreSQL

提问by harman_kardon

I'm using the %% operator on PostgreSQL's hstoretype which converts a hstore (key-value type effectively) into an array whose elements alternate {{key, value}, {key value}}.

我在 PostgreSQL 的hstore类型上使用 %% 运算符，它将 hstore（有效的键值类型）转换为一个数组，其元素交替 {{key, value}, {key value}}。

When I want to return array of these flattened hstores I get this error: could not find array type for data type text[]due to PostgreSQL lack of support for an array of arrays.

当我想返回这些扁平化的 hstore 数组时，我收到此错误：could not find array type for data type text[]由于 PostgreSQL 缺乏对数组数组的支持。

From a curiosity standpoint, does anyone know why these are not supported? And more importantly, is there a work around for this type of scenario?

从好奇的角度来看，有谁知道为什么不支持这些？更重要的是，这种情况是否有解决方法？

At the moment I'm concatenating the results into a string (comma separated) and parsing them on the application (C# and NPGSQL) side. However, this approach doesn't feel quite right, I'd like to be able to read the row back as a .NET array of arrays or array of key-values etc.

目前我将结果连接成一个字符串（逗号分隔）并在应用程序（C# 和 NPGSQL）端解析它们。但是，这种方法感觉不太正确，我希望能够将行作为 .NET 数组数组或键值数组等读回。

Many thanks.

非常感谢。

回答by Peter Krauss

PostgreSQL has limited "array of arrays" support

PostgreSQL 对“数组数组”的支持有限

see manual

见手册

It is a restricted form of "array of arrays". As Pavel (answer) says, it is named "multidimensional array" but is really a matrix, so it must have the same number of elements in each dimension.

它是“数组数组”的一种受限形式。正如Pavel（答案）所说，它被命名为“多维数组”，但实际上是一个矩阵，因此它在每个维度中必须具有相同数量的元素。

You can use this kind of structure for map multidimensional and heterogeneous cartesian coordinates in scientific applications, but not to store arbitrary vectors of vectors like a XML or JSON data.

您可以在科学应用程序中使用这种结构来映射多维和异构笛卡尔坐标，但不能像 XML 或 JSON 数据那样存储向量的任意向量。

NOTE: a well-known 2-dimensional (2D) homogeneous array is the mathematical matrix. In fact, the scientific applications of matrix that motivated the "PostgreSQL constrained multidimensional array" datatype, and the array functions behaviour with these kind of arrays. Think about "3D array" as a "3D matrix", "4D array" as a "4D matrix", and so on.

注意：众所周知的二维 (2D) 齐次数组是数学矩阵。事实上，激发“PostgreSQL 约束多维数组”数据类型的矩阵的科学应用，以及这些类型数组的数组函数行为。将“3D 阵列”视为“3D 矩阵”，将“4D 阵列”视为“4D 矩阵”，依此类推。

EXAMPLES:

例子：

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
---------------------
 {{1,2},{3,4},{5,6}}
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[[5,6]]); -- SAME RESULT

SELECT ARRAY[ARRAY[1,2],ARRAY[5,6]];
---------------
 {{1,2},{5,6}}

SELECT array_cat(ARRAY[ARRAY[1,2]],ARRAY[3]); -- ERROR1
SELECT ARRAY[ARRAY[1,2],ARRAY[4]];  -- ERROR2 

The comments of @Daniel_Lyons about "why these are not supported" is about "non-uniform arrays of arrays" (see error cases above). ERROR1above: because can only concatenate arrays of same dimension ERROR2above: all arrays for a specific dimension must have the same length, like a matrix.

@Daniel_Lyons 关于“为什么不支持这些”的评论是关于“非均匀数组数组”（参见上面的错误案例）。 ERROR1上面：因为上面只能连接相同维度的 ERROR2数组：特定维度的所有数组必须具有相同的长度，就像矩阵一样。

Another curious thing about build-in functions and operators: the "default behaviour" in PostgreSQL is for single arrays and elements. There are no overload for standard array_append(),

关于内置函数和运算符的另一个奇怪的事情是：PostgreSQL 中的“默认行为”是针对单个数组和元素的。标准没有过载array_append()，

SELECT array_append(ARRAY[1,2],5); -- now ok, 5 is a element
 {1,2,5}

SELECT array_cat(ARRAY[1,2], ARRAY[5,6]);
----------
 {1,2,5,6}

SELECT array_append(ARRAY[[1,2],[3,4]], ARRAY[5,6]); -- ERROR3 
SELECT array_append(ARRAY[1,2],ARRAY[5,6]); -- ERROR4

ERROR3above: there are NO OVERLOAD to append "array element" (even 9.2 pg version). ERROR4above: must use array_cat to "merge all in one array".

ERROR3以上：没有 OVERLOAD 附加“数组元素”（即使是 9.2 pg 版本）。 ERROR4以上：必须使用 array_cat 来“将所有内容合并为一个数组”。

The "merge behaviour" of the last array_catexample is curious, not produced array of arrays. Use array_cat(a1, ARRAY[a2])for achieve this result,

最后一个array_cat例子的“合并行为”很奇怪，不是生成的数组。使用array_cat(a1, ARRAY[a2])为实现这一结果，

SELECT array_cat(ARRAY[1,2], ARRAY[ARRAY[5,6]]);  -- seems illogical...
---------------
{{1,2},{5,6}}

---

---

Sparse matrix

稀疏矩阵

To avoid problems with sparse matrixand similar data structures, use the function below. It fills the remaining elements, setting then to NULL (or to any constant value).

为了避免稀疏矩阵和类似数据结构的问题，请使用下面的函数。它填充剩余的元素，然后设置为 NULL（或任何常量值）。

 CREATE or replace FUNCTION array_fillTo(
    p_array anyarray, p_len integer, p_null anyelement DEFAULT NULL
 ) RETURNS anyarray AS $f$
   SELECT CASE 
       WHEN len=0 THEN array_fill(p_null,array[p_len])
       WHEN len<p_len THEN p_array || array_fill(,array[-len])
       ELSE  END
   FROM ( SELECT COALESCE( array_length(p_array,1), 0) ) t(len)
 $f$ LANGUAGE SQL IMMUTABLE;

PS: please edit this answer to add any corrections/optimizations, it is a Wiki!

PS：请编辑此答案以添加任何更正/优化，它是一个 Wiki！

Returning to the first examples, now we can avoid errors (see ERROR1),

回到第一个例子，现在我们可以避免错误（参见 ERROR1），

SELECT array_cat(ARRAY[ARRAY[1,2]],array_fillTo(ARRAY[3],2));
-- {{1,2},{3,NULL}}
SELECT array_cat(
   ARRAY[ARRAY[1.1::float,2.0]],
   array_fillTo(ARRAY[]::float[],2,0::float)
);
-- {{1.1,2},{0,0}}
SELECT array_fillto(array['Hello'],2,'');
-- {Hello,""}

---

---

NOTE about old array_fillTo()

关于旧 array_fillTo() 的注意事项

The array_fill()become a buildin function with PostgreSQL v8.4, for v8.3 or olds:

将array_fill()成为在PostgreSQL V8.4一的buildin功能，V8.3或孩子：

 CREATE FUNCTION array_fillTo(anyarray,integer,anyelement DEFAULT NULL) 
 RETURNS anyarray AS $$
   DECLARE
     i integer;
     len integer;
     ret ALIAS FOR 
CREATE TABLE users (
  id integer primary key,
  name varchar,
  favorite_colors varchar[],
  ...
);
;
   BEGIN
     len = array_length(,1);
     ret = ;
     IF len< THEN
         FOR i IN 1..(-len) LOOP
           ret = ret || ;
         END LOOP;
     END IF;
     RETURN ret;
   END;
 $$ LANGUAGE plpgsql IMMUTABLE;

回答by Daniel Lyons

From a curiosity standpoint, does anyone know why these are not supported?

从好奇的角度来看，有谁知道为什么不支持这些？

One generic answer is because arrays are intrinsically anti-relational. Removing repeating values is how you achieve 1st normal form. To have repeating groups of repeating groups seems quite insane from a relational theoretical standpoint.

一个通用的答案是因为数组本质上是反关系的。删除重复值是您实现第一范式的方法。从关系理论的角度来看，拥有重复组的重复组似乎很疯狂。

In general, the relationally-correct thing to do is to extract a table for your repeating values. So if you modeled something like this:

一般来说，关系正确的做法是为您的重复值提取一个表格。因此，如果您对以下内容进行建模：

CREATE TABLE users (
  id integer primary key,
  name varchar,
  ...
);

CREATE TABLE favorite_colors (
  user_id integer references users,
  color varchar
);

it would behoove you to redefine this relationally like so:

你应该像这样重新定义这个关系：

CREATE TABLE users (
  id integer primary key,
  name varchar,
  ...
);

CREATE TABLE colors (
  color varchar primary key
);

CREATE TABLE favorite_colors (
  user_id integer references users,
  color varchar references colors,
  primary key (user_id, color)
);

Or even:

甚至：

postgres=# create table fx(a int[]);
CREATE TABLE
postgres=# insert into fx values(array[1,3,4]);
INSERT 0 1
postgres=# insert into fx values(array[6,7]);
INSERT 0 1
postgres=# select array_agg(row(a)) from fx;
            array_agg            
---------------------------------
 {"(\"{1,3,4}\")","(\"{6,7}\")"}
(1 row)

Hstore supports a lot of functions, many of which would make it easy to integrate it into a relational worldview. I think the simplest way to solve your problem would be to use the eachfunction to convert your hstore values into relations you can then use like a normal set of values. This is how you address having multiple values in other databases anyway: querying, and working with result sets.

Hstore 支持许多功能，其中许多功能可以轻松将其集成到关系世界观中。我认为解决您的问题的最简单方法是使用该each函数将您的 hstore 值转换为您可以像使用普通值集一样使用的关系。无论如何，这就是解决在其他数据库中具有多个值的方法：查询和使用结果集。

回答by Pavel Stehule

PostgreSQL support a multidimensional arrays instead - arrays are relative very special type in relational databases and it is little bit limited against general programming languages. If you need it, you can use a workaround with row arrays:

PostgreSQL 支持多维数组 - 数组是关系数据库中相对非常特殊的类型，它对通用编程语言有一点限制。如果需要，可以对行数组使用解决方法：

##代码##