8.15. Arrays
PostgreSQL 允许将表的列定义为可变长度的多维数组。可以创建任何内置或用户定义的基本类型，枚举类型或复合类型的数组。尚不支持域数组。
8 .15.1. 数组类型的声明
为了说明数组类型的用法，我们创建此表：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
如图所示，通过将方括号([])附加到数组元素的数据类型名称来命名数组数据类型。上面的命令将创建一个名为sal_emp的表，该表的列类型为text(name)，一维类型为integer(pay_by_quarter)的一维数组，按季度代表员工的薪水，而二维数组为text(schedule)。 ，代表员工的每周时间表。
CREATE TABLE的语法允许指定数组的确切大小，例如：
CREATE TABLE tictactoe (
squares integer[3][3]
);
但是，当前实现会忽略任何提供的数组大小限制，即行为与未指定长度的数组相同。
当前实现也不执行声明的维数。无论大小或维数如何，特定元素类型的数组都被认为是同一类型。因此，在CREATE TABLE中声明数组的大小或维数只是文档。它不会影响运行时行为。
通过使用关键字ARRAY符合 SQL 标准的另一种语法可以用于一维数组。 pay_by_quarter可以定义为：
pay_by_quarter integer ARRAY[4],
或者，如果不指定数组大小：
pay_by_quarter integer ARRAY,
但是，和以前一样，PostgreSQL 在任何情况下都不会实施大小限制。
8 .15.2. 数组值 Importing
要将数组值写为 Literals 常量，请将元素值括在花括号中，并用逗号分隔。 (如果您知道 C，这与用于初始化结构的 C 语法没有什么不同.)您可以在任何元素值两边加上双引号，并且如果包含逗号或花括号，则必须这样做。 (更多详细信息显示在下面.)因此，数组常量的一般格式如下：
'{ val1 delim val2 delim ... }'
其中* delim 是该类型的分隔符，记录在其pg_type条目中。在 PostgreSQL 发行版中提供的标准数据类型中，所有类型都使用逗号(,)，但类型box使用分号(;)除外。每个 val *要么是数组元素类型的常量，要么是子数组。数组常量的一个示例是：
'{{1,2,3},{4,5,6},{7,8,9}}'
该常数是一个二维的 3×3 数组，由三个整数子数组组成。
要将数组常量的元素设置为 NULL，请为该元素值写NULL。 (NULL的任何大写或小写变体都可以.)如果要实际的字符串值“ NULL”，则必须在其前后加上双引号。
(这些数组常量实际上只是Section 4.1.2.7中讨论的泛型类型常量的特例。该常量最初被视为字符串，并传递给数组 Importing 转换例程。可能需要显式类型说明。)
现在我们可以显示一些INSERT语句：
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
前两次插入的结果如下所示：
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
-------+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
多维数组必须具有每个维的匹配范围。不匹配会导致错误，例如：
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: multidimensional arrays must have array expressions with matching dimensions
也可以使用ARRAY构造函数语法：
INSERT INTO sal_emp
VALUES ('Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
请注意，数组元素是普通的 SQL 常量或表达式。例如，字符串 Literals 用单引号引起来，而不是像在数组 Literals 中那样用双引号引起来。 Section 4.2.12中将详细讨论ARRAY构造函数语法。
8 .15.3. 访问数组
现在，我们可以在表上运行一些查询。首先，我们展示如何访问数组的单个元素。此查询检索第二季度薪水已更改的员工的姓名：
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)
数组下标数字写在方括号内。缺省情况下，PostgreSQL 对数组使用基于一个编号的约定，即* n *元素的数组以array[1]开头并以array[n]结尾。
此查询检索所有员工的第三季度工资：
SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
10000
25000
(2 rows)
我们还可以访问数组或子数组的任意矩形切片。通过为一个或多个数组维写lower-bound:upper-bound来表示数组切片。例如，此查询在一周的前两天检索比尔时间表中的第一项：
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
如果将任何维度写为切片，即包含冒号，则所有维度都将视为切片。任何只有一个数字(无冒号)的维将被视为从 1 到指定的数字。例如，在此示例中，将[2]视为[1:2]：
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------------------------------
{{meeting,lunch},{training,presentation}}
(1 row)
为避免与非切片情况混淆，最好对所有维度使用切片语法，例如[1:2][1:1]而不是[2][1:1]。
可以省略切片说明符的* lower-bound 和/或 upper-bound *；缺少的边界将替换为数组下标的下限或上限。例如：
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{lunch},{presentation}}
(1 row)
SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
如果数组本身或任何下标表达式为 null，则数组下标表达式将返回 null。另外，如果下标超出数组范围，则返回 null(这种情况不会引发错误)。例如，如果schedule当前具有尺寸[1:3][1:2]，则引用schedule[3][3]会产生 NULL。同样，下标数目错误的数组引用会产生 null 而不是错误。
如果数组本身或任何下标表达式为 null，则数组切片表达式同样会产生 null。但是，在其他情况下，例如选择完全超出当前数组范围的数组切片，切片表达式会产生一个空(零维)数组，而不是 null。 (这与非切片行为不匹配，并且由于历史原因而完成.)如果请求的切片部分与数组边界重叠，则将其静默地缩小为重叠区域，而不是返回 null。
可以使用array_dims函数检索任何数组值的当前维：
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
array_dims
------------
[1:2][1:2]
(1 row)
array_dims产生text的结果，这对人们来说很方便阅读，但对于程序来说可能不方便。也可以使用array_upper和array_lower检索维，这将分别返回指定数组维的上限和下限：
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_upper
-------------
2
(1 row)
array_length将返回指定数组维的长度：
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_length
--------------
2
(1 row)
cardinality返回所有维度上数组中元素的总数。实际上，调用unnest会产生的行数：
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
cardinality
-------------
4
(1 row)
8 .15.4. 修改数组
数组值可以完全替换：
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
或使用ARRAY表达式语法：
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
数组也可以在单个元素上更新：
UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';
或切片中更新：
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';
也可以使用省略了* lower-bound 和/或 upper-bound *的切片语法，但是仅当更新非 NULL 或零维的数组值时(否则，不存在可替换的下标限制)。
可以通过分配不存在的元素来扩大存储的数组值。先前存在的元素和新分配的元素之间的任何位置都将填充为空。例如，如果数组myarray当前具有 4 个元素，则在分配给myarray[6]的更新后它将具有六个元素； myarray[5]将包含空值。当前，仅允许一维数组而不是多维数组以这种方式进行扩展。
下标分配允许创建不使用基于一的下标的数组。例如，可能分配给myarray[-2:7]来创建一个数组，其下标值为-2 到 7.
也可以使用串联运算符||构造新的数组值：
SELECT ARRAY[1,2] || ARRAY[3,4];
?column?
-----------
{1,2,3,4}
(1 row)
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
?column?
---------------------
{{5,6},{1,2},{3,4}}
(1 row)
串联运算符允许将单个元素推到一维数组的开头或结尾。它还接受两个* N -维数组，或者 N -维数组和 N+1 *-维数组。
当将单个元素推到一维数组的开始或结尾时，结果是一个下标与数组操作数相同的数组。例如：
SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
array_dims
------------
[0:2]
(1 row)
SELECT array_dims(ARRAY[1,2] || 3);
array_dims
------------
[1:3]
(1 row)
当连接两个维数相等的数组时，结果将保留左侧操作数外部维的下限下标。结果是一个数组，其中包含左操作数的每个元素，后跟右操作数的每个元素。例如：
SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
array_dims
------------
[1:5]
(1 row)
SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
array_dims
------------
[1:5][1:2]
(1 row)
将* N 维数组推到 N+1 *维数组的开头或结尾时，结果类似于上述元素数组的情况。每个N *维子数组本质上是N+1 *维数组的外部维的元素。例如：
SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
array_dims
------------
[1:3][1:2]
(1 row)
也可以使用函数array_prepend，array_append或array_cat构造数组。前两个仅支持一维数组，但是array_cat支持多维数组。一些例子：
SELECT array_prepend(1, ARRAY[2,3]);
array_prepend
---------------
{1,2,3}
(1 row)
SELECT array_append(ARRAY[1,2], 3);
array_append
--------------
{1,2,3}
(1 row)
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
array_cat
-----------
{1,2,3,4}
(1 row)
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
array_cat
---------------------
{{1,2},{3,4},{5,6}}
(1 row)
SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
array_cat
---------------------
{{5,6},{1,2},{3,4}}
在简单情况下，与直接使用这些函数相比，上面讨论的串联运算符更为可取。但是，由于串联运算符被重载以服务所有这三种情况，因此在某些情况下，使用功能之一有助于避免歧义。例如考虑：
SELECT ARRAY[1, 2] || '{3, 4}'; -- the untyped literal is taken as an array
?column?
-----------
{1,2,3,4}
SELECT ARRAY[1, 2] || '7'; -- so is this one
ERROR: malformed array literal: "7"
SELECT ARRAY[1, 2] || NULL; -- so is an undecorated NULL
?column?
----------
{1,2}
(1 row)
SELECT array_append(ARRAY[1, 2], NULL); -- this might have been meant
array_append
--------------
{1,2,NULL}
在上面的示例中，解析器在串联运算符的一侧看到一个整数数组，在另一侧看到一个不确定类型的常量。它用来解析常量类型的试探法是假设它与运算符的其他 Importing 具有相同的类型，在本例中为整数数组。因此，假定串联运算符表示array_cat而不是array_append。如果那是错误的选择，则可以通过将常量转换为数组的元素类型来解决。但是显式使用array_append可能是更可取的解决方案。
8 .15.5. 在数组中搜索
要在数组中搜索值，必须检查每个值。如果您知道阵列的大小，则可以手动完成。例如：
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
但是，这对于大型阵列很快变得很繁琐，如果阵列的大小未知，则无济于事。 Section 9.23中描述了一种替代方法。上面的查询可以替换为：
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
此外，您可以使用以下命令查找数组的所有值均等于 10000 的行：
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
或者，可以使用generate_subscripts功能。例如：
SELECT * FROM
(SELECT pay_by_quarter,
generate_subscripts(pay_by_quarter, 1) AS s
FROM sal_emp) AS foo
WHERE pay_by_quarter[s] = 10000;
Table 9.59中描述了此功能。
您还可以使用&&运算符搜索数组，该运算符将检查左操作数是否与右操作数重叠。例如：
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
Section 9.18中进一步描述了此数组运算符和其他数组运算符。可以通过适当的索引来加速，如Section 11.2中所述。
您还可以使用array_position和array_positions函数在数组中搜索特定值。前者返回数组中第一次出现的值的下标；后者返回一个数组，其中包含该数组中所有出现的值的下标。例如：
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_positions
-----------------
2
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
array_positions
-----------------
{1,4,8}
Tip
数组不是集合；搜索特定的数组元素可能是数据库设计错误的标志。考虑使用单独的表，该表的每个行都将是一个数组元素。这将更易于搜索，并且可能会针对大量元素进行更好地缩放。
8 .15.6. 数组 Importing 和输出语法
数组值的外部文本表示由根据数组元素类型的 I/O 转换规则解释的项目以及表示数组结构的修饰组成。装饰由数组值周围的花括号({和})以及相邻项目之间的定界符组成。分隔符通常是逗号(,)，但可以是其他字符：它由数组元素类型的typdelim设置确定。在 PostgreSQL 发行版中提供的标准数据类型中，除了使用分号(;)的box类型之外，所有数据类型都使用逗号。在多维数组中，每个维度(行，平面，立方体等)都有其自己的花括号级别，并且必须在同一级别的相邻花括号实体之间编写分隔符。
如果数组值是空字符串，包含大括号，定界符，双引号，反斜杠或空格，或者与单词_匹配，则数组输出例程将在元素值两边加上双引号。元素值中嵌入的双引号和反斜杠将被反斜杠转义。对于数字数据类型，可以安全地假定双引号将永远不会出现，但是对于文本数据类型，应准备使用双引号来应对是否存在引号。
默认情况下，数组维的下界索引值设置为 1.为了表示具有其他下限的数组，可以在写入数组内容之前显式指定数组下标范围。此修饰由围绕每个数组维度的上下边界的方括号([])组成，中间用冒号(:)分隔符。数组维修饰后跟一个等号(=)。例如：
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
e1 | e2
----+----
1 | 6
(1 row)
仅当存在一个或多个与下界不同的下限时，数组输出例程才会在其结果中包括显式维。
如果为元素写入的值为NULL(在任何情况下都是变体)，则该元素被视为 NULL。如果存在任何引号或反斜杠，则会禁用此功能，并允许 ImportingLiterals 字符串值“ NULL”。另外，为了与 8.2 之前的 PostgreSQL 版本向后兼容，可以将array_nulls配置参数设置为off，以禁止将NULL识别为 NULL。
如前所示，在编写数组值时，可以在任何单个数组元素周围使用双引号。如果元素值否则会使数组值解析器混乱，则必须*这样做。例如，包含大括号，逗号(或数据类型的定界符)，双引号，反斜杠或前导或尾随空格的元素必须用双引号引起来。空字符串和与单词NULL匹配的字符串也必须加引号。要将双引号或反斜杠放在带引号的数组元素值中，请在其前面加上反斜杠。另外，您可以避免使用引号，并使用反斜杠转义来保护所有数据字符，否则这些字符将被视为数组语法。
您可以在左括号前或右括号后添加空格。您还可以在任何单个项目字符串之前或之后添加空格。在所有这些情况下，空格都将被忽略。但是，不能忽略双引号元素内的空格，或者在两侧被元素的非空格字符包围的空格。
Tip
当在 SQL 命令中写入数组值时，使用ARRAY构造函数语法(请参见Section 4.2.12)通常比使用数组 Literals 语法更容易使用。在ARRAY中，单个元素值的写入方式与非数组成员时的写入方式相同。