8.14. 数组

8.14.1. 数组类型的声明

为说明这些用法，我们先创建一个由基本类型数组构成的表：

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

如上所示，一个数组类型是通过在数组元素类型名后面附加方括弧([])来命名的 上面的命令将创建一个叫sal_emp的表， 表示雇员名字的name字段是一个text类型字符串， 表示雇员季度薪水的pay_by_quarter字段是一个一维integer数组， 表示雇员周计划的schedule字段是一个两维text数组。

CREATE TABLE的语法允许声明数组的确切大小，比如：

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

不过，目前的实现并不强制数组尺寸限制(等价于未声明长度的数组)。

实际上，目前的实现也不强制数组维数。 特定元素类型的数组都被认为是相同的类型，不管他们的大小或者维数。 因此，在CREATE TABLE里定义数字或者维数都不影响运行时的行为。

另外还有一种语法，它遵循SQL标准，运用主键ARRAY，可以用于声明一维数组。 pay_by_quarter可以定义为：

    pay_by_quarter  integer ARRAY[4],

或者，如果没有指定数组大小：

    pay_by_quarter  integer ARRAY,

不过，如前所述，PostgreSQL并不强制这个尺寸限制。

8.14.2. 数组值输入

将数组写成文本的时候， 用花括弧把数组元素括起来并且用逗号将它们分开(如果你懂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"，那么你必须加上双引号。

这种数组常量实际上只是我们在Section 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常量或者表达式； 比如，字符串文本是用单引号包围的，而不是像数组文本那样用双引号。 ARRAY构造器语法在Section 4.2.11里有更详细的讨论。

8.14.3. 访问数组

现在我们可以在这个表上运行一些查询。 首先，我们演示如何一次访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名:

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-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]。

如果数组本身或者任何一个下标表达式是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)

8.14.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';

可以通过给尚不存在的数组元素赋值的办法来扩大数组， 所有位于原数组最后一个元素和这个新元素之间的未赋值元素都将设为NULL。 例如，如果myarray数组当前有4个元素，在对myarray[6]赋值之后它将拥有6个元素， 其中myarray[5]的值将为NULL。 目前，只允许对一维数组使用这种方法扩大，而不是多维数组。

下标赋值允许创建下标不从1开始的数组。 比如，我们可以通过给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}}

8.14.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.21里为大家描述了另外一个方法。 上面的查询可以用下面的代替：

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-46中有对该函数的说明。

8.14.6. 数组的输入和输出语法

一个数组值的外部表现形式由一些根据该数组元素类型的I/O转换规则分析的项组成， 再加上一些标明该数组结构的修饰 这些修饰由围绕在数组值周围的花括弧({ and })加上相邻项之间的分隔字符组成。 分隔字符通常是一个逗号(,)，但也可以是其它的东西：它由该数组元素类型的typdelim设置决定。 在PostgreSQL提供的标准数据类型里，除了box类型使用分号(;)外,所有其它类型都使用逗号。 在多维数组里，每个维都有自己级别的花括弧，并且在同级相邻的花括弧项之间必须写上分隔符。

如果数组元素值是空字符串或者包含花括弧、分隔符、双引号、反斜杠、空白， 或者匹配NULL关键字，那么数组输出过程将在这些值周围包围双引号。 在元素值里包含的双引号和反斜杠将被反斜杠逃逸。 对于数值类型，你可以安全地假设数值没有双引号包围， 但是对于文本类型，我们就需要准备好面对有双引号包围和没有双引号包围两种情况了。

缺省时，一个数组维数的下标索引设置为1。 如果一个数组维数下标不等于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"值。 另外，为了兼容8.2之前的版本， 可以将array_nulls配置参数设为off以禁止将NULL识别为NULL。

如前所示，当书写一个数组值的时候，可以在任何元素值周围加上双引号。 当元素值可能让数组值解析器产生歧义时，就必须在元素周围加上双引号，例如：元素值包含花括号、逗号(或者是其它分割符)、双引号、反斜杠、在开头/结尾处有空白符、匹配NULL的字符串。 要在元素值中包含双引号或反斜杠，可以加一个前导反斜杠。 当然，你也可以使用反斜杠逃逸来保护任何可能引起语法混淆的字符。

你可以在左花括弧前面或者右花括弧后面写空白，也可以在任意独立的项字符串前面或者后面写空白。 所有这些情况下，空白都会被忽略。 不过，在双引号包围的元素里面的空白，或者是元素里被两边非空白字符包围的空白，不会被忽略。