第 6 章 数组

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

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)

Postgres对数组使用“从 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 个元素。目前，只允许以这种方式扩大一维数组，不允许扩大多维数组。

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

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

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

实际上，当前的实现也不强制声明的维数。特定基本类型（base type）的数组无论大小或维数如何，都被视为同一类型。

任何数组值的当前维度都可以用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 的主发行版，但在贡献目录中，有一个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目录。