登录8.15 数组#
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 数组值输入#
要将数组值写为文字常量，请用大括号括起元素值，并用逗号分隔。 （如果您了解 C 语言，这与 C 语言中初始化结构的语法非常相似。）您可以在任何元素值周围加上双引号，如果元素值包含逗号或大括号，则必须加上双引号。 （更多详细信息见下文。）因此，数组常量的一般格式如下
'{ val1 delim val2 delim ... }'其中*delim是该类型的分隔符，记录在其pg_type条目中。在PostgreSQL发行版中提供的标准数据类型中，所有类型都使用逗号 (,)，除了类型box，它使用分号 (;)。每个val*都是数组元素类型的常量或子数组。数组常量的示例是
'{{1,2,3},{4,5,6},{7,8,9}}'此常量是一个二维 3x3 数组，由三个整数子数组组成。
要将数组常量的元素设置为 NULL，请为元素值编写NULL。（NULL的任何大小写变体都可以。）如果您想要实际的字符串值“NULL”，则必须用双引号将其引起来。
（这些类型的数组常量实际上只是第 4.1.2.7 节中讨论的通用类型常量的特例。该常量最初被视为字符串并传递给数组输入转换例程。可能需要显式类型规范。）
现在我们可以展示一些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 常量或表达式；例如，字符串文字是单引号，而不是像在数组文字中那样用双引号引起来。在第 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*，用于一个或多个数组维度。例如，此查询检索 Bill 的日程表中前两天的第一项
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 或零维数组值时（否则，没有现有的下标限制可替代）。
可以通过分配给尚未存在的元素来扩大存储的数组值。以前存在的元素和新分配的元素之间的任何位置都将填充为 null。例如，如果数组myarray当前有 4 个元素，则在分配给myarray[6]的更新后，它将有 6 个元素；myarray[5]将包含 null。目前，仅允许一维数组以这种方式扩大，不允许多维数组。
下标赋值允许创建不使用基于一的子脚本的数组。例如，可以分配给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}在上面的示例中，解析器在连接运算符的一侧看到一个整数数组，在另一侧看到一个类型未定的常量。它用于解析常量类型的启发式方法是假设它与运算符的另一个输入类型相同——在本例中为整数数组。因此，连接运算符被假定为表示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;但是，对于大型数组，这很快就会变得乏味，并且如果数组的大小未知，则无济于事。替代方法在第 9.24 节中进行了描述。上面的查询可以用以下内容替换
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;此函数在表 9.66中进行了描述。
你还可以使用&&运算符搜索数组，该运算符检查左操作数是否与右操作数重叠。例如
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];此数组运算符和其他数组运算符在第 9.19 节中进行了进一步描述。它可以通过适当的索引进行加速，如第 11.2 节中所述。
您还可以使用array_position和array_positions函数在数组中搜索特定值。前者返回数组中某个值首次出现的下标；后者返回一个数组，其中包含数组中该值的所有出现下标。例如
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_position
----------------
2
(1 row)
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
array_positions
-----------------
{1,4,8}
(1 row)提示
数组不是集合；搜索特定数组元素可能是数据库设计不当的标志。考虑使用一个单独的表，其中每一行对应一个将成为数组元素的项。这将更容易搜索，并且对于大量元素来说，可能会更好地扩展。
8.15.6. 数组输入和输出语法#
数组值的外部文本表示由根据数组元素类型的 I/O 转换规则解释的项以及指示数组结构的修饰组成。修饰由数组值周围的花括号 ({和}) 以及相邻项之间的分隔符字符组成。分隔符字符通常是逗号 (,)，但也可以是其他字符：它由数组元素类型的typdelim设置决定。在PostgreSQL发行版中提供的标准数据类型中，除了使用分号 (;) 的类型box之外，所有类型都使用逗号。在多维数组中，每个维度（行、平面、立方体等）都有自己级别的花括号，并且必须在同一级别的相邻花括号实体之间编写分隔符。
如果数组输出例程的元素值为空字符串、包含花括号、分隔符字符、双引号、反斜杠或空格，或者与单词NULL匹配，则会在元素值周围加上双引号。嵌入在元素值中的双引号和反斜杠将被反斜杠转义。对于数字数据类型，可以安全地假设永远不会出现双引号，但对于文本数据类型，应该准备好应对引号的存在或不存在。
默认情况下，数组维度的下限索引值设置为 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)仅当有一个或多个下限与 1 不同时，数组输出例程才会在其结果中包含显式维度。
如果为元素编写的值为NULL（在任何变体中），则该元素将被视为 NULL。任何引号或反斜杠的存在都会禁用此功能，并允许输入文本字符串值“NULL”。此外，为了与PostgreSQL的 8.2 之前的版本向后兼容，可以关闭array_nulls配置参数以禁止识别NULL为 NULL。
如前所示，当编写数组值时，可以在任何单个数组元素周围使用双引号。如果元素值会混淆数组值解析器，则必须这样做。例如，包含花括号、逗号（或数据类型的分隔符字符）、双引号、反斜杠或前导或尾随空格的元素必须用双引号引起来。空字符串和与单词NULL匹配的字符串也必须用引号引起来。要在带引号的数组元素值中放入双引号或反斜杠，请在其前面加上反斜杠。或者，您可以避免使用引号，并使用反斜杠转义来保护所有数据字符，否则这些字符将被视为数组语法。
您可以在左大括号前或右大括号后添加空格。您还可以在任何单个项目字符串前或后添加空格。在所有这些情况下，空格都将被忽略。但是，双引号元素中的空格，或被元素的非空格字符包围在两侧的空格不会被忽略。
提示
在 SQL 命令中编写数组值时，ARRAY构造函数语法（请参见第 4.2.12 节）通常比数组字面语法更容易使用。在ARRAY中，单个元素值以与作为数组成员时相同的方式编写。