第 6 章 数组

PostgreSQL允许把表的列定义为变长的 多维数组。可以创建任何内建类型或用户定义类型的数组。为了说明 它们的用途，我们创建这个表：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

如上所示，数组数据类型通过在数组元素的数据类型名称后面追加 方括号（[]）来命名。上面的查询将创建一个名为 sal_emp的表，其中的列包括一个 text字符串（name）、 一个integer类型的一维数组 （pay_by_quarter，表示员工的季度 薪水）以及一个text类型的二维数组 （schedule，表示员工的周计划）。

现在我们做一些INSERT。注意，要写一个数组值， 需要把元素值用花括号括起来并用逗号分隔。如果你了解 C，这很像 C 中初始化结构的语法。（更多细节见下文。）

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"talk", "consult"}, {"meeting"}}');

现在，我们可以在sal_emp上运行一些查询。 首先，我们展示如何一次访问数组的一个元素。下面的查询检索 第二季度薪水发生变化的员工的姓名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

数组的下标编号写在方括号内。默认情况下， PostgreSQL对数组使用“从 1 开始”的 编号约定，也就是说，一个有n个元素的数组从 array[1]开始，以array[n]结束。

这个查询检索所有员工的第三季度薪水：

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

我们也可以访问数组的任意矩形切片或子数组。数组切片通过为 一个或多个数组维度写 lower subscript : upper subscript来表示。下面的查询检索 Bill 周计划前两天的第一项：

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

      schedule
--------------------
 {{meeting},{""}}
(1 row)

我们也可以写成

SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';

结果相同。只要任何一个下标写成了 lower : upper 的形式，数组下标操作就表示一个数组切片。对于只指定了 一个值的下标，会假定其下界为 1。

可以完整地替换一个数组值：

UPDATE sal_emp SET pay_by_quarter = '{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';

通过向与已有元素相邻的元素赋值，或向与已有数据相邻或重叠的 切片赋值，可以扩大数组。例如，如果一个数组值当前有 4 个元素， 那么向array[5]赋值的更新完成后它将有 5 个元素。 目前，只允许以这种方式扩大一维数组，不允许扩大多维数组。

数组切片赋值允许创建不使用从 1 开始下标的数组。例如，可以 向array[-2:7]赋值来创建一个下标值从 -2 到 7 的 数组。

CREATE TABLE的语法允许定义定长数组：

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

然而，当前的实现并不强制数组的大小限制——其行为与未指定 长度的数组相同。

实际上，当前的实现也不强制声明的维数。特定元素类型的数组 无论大小或维数如何，都被视为同一类型。因此，在 CREATE TABLE中声明维数或大小只是文档性的， 并不影响运行时行为。

任何数组值的当前维度都可以用array_dims 函数检索：

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:1]
(1 row)

array_dims产生一个text类型的 结果，便于人阅读，但对程序来说可能不太方便。

要在数组中搜索一个值，必须检查数组的每个值。这可以手工完成 （如果你知道数组的大小）：

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;

然而，对于大型数组这很快就会变得繁琐，而且在数组大小未知时 也帮不上忙。虽然它不属于PostgreSQL 的主发行版，但有一个可用的扩展为遍历数组值定义了新的函数和 操作符。使用它，上面的查询可以写成：

SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;

要搜索整个数组（而不只是指定的列），可以使用：

SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;

此外，你还可以找出数组中所有值都等于 10 000 的行：

SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;

要安装这个可选模块，请查看PostgreSQL 源码发行版中的contrib/array目录。

数组输入和输出语法。.  数组值的外部表示由若干项构成，这些项按照数组元素类型的 I/O 转换规则解释，外加指示数组结构的装饰。装饰由环绕数组值的 花括号（{和}）以及相邻项之间的分隔符 字符组成。分隔符通常是逗号（,），但也可以是别的： 它由数组元素类型的typdelim设置决定。（在 PostgreSQL发行版提供的标准数据类型（datatypes）中， box类型使用分号（;），其他所有类型都 使用逗号。）在多维数组中，每个维度（行、平面、立方体等）都有 自己的一层花括号，并且在同层的、由花括号括起的相邻各项之间必须写上 分隔符。你可以在左花括号之前、右花括号之后或任何单个项字符串 之前写空白。但项后面的空白不会被忽略：跳过前导空白后， 直到下一个右花括号或分隔符之前的所有内容都被当作项的值。

引用数组元素。.  如上所示，写数组值时可以给任何单个数组元素加双引号。如果 元素值会导致数组值解析器混淆，你必须这样做。 例如，包含花括号、逗号（或任何分隔符字符）、双引号、反斜线或 前导空白的元素必须加双引号。要在数组元素值中放入双引号或 反斜线，需要在它前面加一个反斜线。另外，你也可以使用反斜线 转义来保护所有会被当作数组语法或可忽略空白的数据字符。

如果元素值是空字符串或者包含花括号、分隔符字符、双引号、 反斜线或空白，数组输出例程会给它们加上双引号。元素值中 内嵌的双引号和反斜线将被反斜线转义。对于数字数据类型（datatypes）， 可以放心地假设永远不会出现双引号，但对于文本数据类型（datatypes）， 应当准备同时应对有无引号两种情况。（这是与 7.2 之前的 PostgreSQL版本相比的一个行为变化。）

INSERT ... VALUES ('{"\\\\","\\""}');