8.15. Arrays · PostgreSQL 中文文档 9.3

# 8.15\. Arrays PostgreSQL允许将字段定义成变长的多维数组。数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。不支持域的数组。 ## 8.15.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` 里定义数字或者维数都不影响运行时的行为。另外还有一种语法，它通过使用关键字`ARRAY`遵循 SQL 标准，可以用于声明一维数组。`pay_by_quarter`可以定义为： ``` pay_by_quarter integer ARRAY[4], ``` 或者不声明数组的大小： ``` pay_by_quarter integer ARRAY, ``` 不过，如前所述，PostgreSQL并不强制这个尺寸限制。 ## 8.15.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](#calibre_link-788) 里讨论过的一般类型常量的一种特例。常量最初是当作字符串看待并且传递给数组输入转换器的，可能需要使用明确的类型声明。现在我们可以展示一些`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.12](#calibre_link-1640) 里有更详细的讨论。 ## 8.15.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) ``` 我们还可以访问一个数组的任意矩形片段，或称子数组。对于一维或更多维数组，可以用`_下标下界_`:`_下标上界_` 表示一个数组的某个片段。比如，下面查询检索 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.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'; ``` 可以通过给一个尚不存在数组元素赋值的办法扩大数组，所有位于原数组最后一个元素和这个新元素之间的未赋值元素都将设为 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.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](#calibre_link-1529) 里描述。上面的查询可以用下面的代替： ``` 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-50](#calibre_link-2151)里面描述。你可以使用`&&`操作符检索一个数组，它可以检查左操作数是否与右操作数重叠。例如： ``` SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000]; ``` 这个操作符和另外一个数组操作符在[Section 9.18](#calibre_link-1526)里有详细的描述。它可以通过一个恰当的索引加速，在[Section 11.2](#calibre_link-714)里面描述。 > **Tip:** 数组不是集合；需要像前面那样搜索数组中的特定元素通常表明你的数据库设计有问题。数组字段通常是可以分裂成独立的表。很明显表要容易搜索得多，并且在元素数目非常庞大的时候也可以更好地伸展。 ## 8.15.6\. 数组输入和输出语法一个数组值的外部表现形式由一些根据该数组元素类型的 I/O 转换规则分析的项组成，再加上一些标明该数组结构的修饰。这些修饰由围绕在数组值周围的花括弧(`{` 和`}`)加上相邻项之间的分隔字符组成。分隔字符通常是一个逗号(`,`) 但也可以是其它的东西：它由该数组元素类型的`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](#calibre_link-2152) 配置参数设为`off`以禁止将`NULL`识别为 NULL 。如前所示，当书写一个数组值的时候，可以在任何元素值周围使用双引号。当元素值可能让数组值解析器产生歧义时，你_必须_这么做。例如：元素值包含花括号、逗号(或者数据类型分割符)、双引号、反斜杠、在开头/结尾处有空白符、匹配 NULL 的字符串。要在元素值中包含双引号或反斜杠，可以加一个前导反斜杠。当然，你也可以避免引用和使用反斜杠逃逸来保护任何可能引起语法混淆的字符。你可以在左花括弧前面或者右花括弧后面写空白。你还可以在任意独立的项字符串前面或者后面写空白。所有这些情况下，这些空白都会被忽略。不过，在双引号包围的元素里面的空白，或者是元素里被两边非空白字符包围的空白，都不会被忽略。 > **Note:** 请记住你在 SQL 命令里写的任何东西都将首先解释成一个字符串文本，然后才是一个数组。这样就造成你所需要的反斜杠数量翻了翻。比如，要插入一个包含反斜杠和双引号的`text`数组，你需要这么写： > > ``` > INSERT ... VALUES (E'{"\\\\","\\""}'); > ``` > > 字符串文本处理器去掉第一层反斜杠，然后剩下的东西到了数组数值分析器的时候将变成 `{"\\","\""}`。接着，该字符串传递给`text`数据类型的输入过程，分别变成`\`和`"`。如果我们使用的数据类型对反斜杠也有特殊待遇，比如`bytea`，那么我们可能需要在命令里放多达八个反斜杠才能在存储态的数组元素中得到一个反斜杠。也可以用美元符界定(参阅[Section 4.1.2.4](#calibre_link-736))来避免双份的反斜杠。 > **Tip:** `ARRAY`构造器语法(参阅[Section 4.2.12](#calibre_link-1640)) 通常比数组文本语法好用些，尤其是在 SQL 命令里写数组值的时候。在`ARRAY`里，独立的元素值的写法和数组里没有元素时的写法一样。