========== 数组 ========== .. container:: sect1 :name: ARRAYS .. container:: titlepage .. container:: .. container:: .. rubric:: 数组 :name: 数组 :class: title .. container:: toc PostgreSQL允许一个表中的列定义为变长多维数组。可以创建任何内建或用户定义的基类、枚举类型、组合类型或者域的数组。 .. container:: sect2 :name: ARRAYS-DECLARATION .. container:: titlepage .. container:: .. container:: .. rubric:: 数组类型的定义 :name: 数组类型的定义 :class: title 为了展示数组类型的使用，我们创建这样一个表： .. code:: programlisting CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); 如上所示，一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（\ ``[]``\ ）来命名。上述命令将创建一个名为\ ``sal_emp``\ 的表，它有一个类型为\ ``text``\ 的列（\ ``name``\ ），一个表示雇员的季度工资的一维\ ``integer``\ 类型数组（\ ``pay_by_quarter``\ ），以及一个表示雇员每周日程表的二维\ ``text``\ 类型数组（\ ``schedule``\ ）。 ``CREATE TABLE``\ 的语法允许指定数组的确切大小，例如： .. code:: programlisting CREATE TABLE tictactoe ( squares integer[3][3] ); 然而，当前的实现忽略任何提供的数组尺寸限制，即其行为与未指定长度的数组相同。 当前的实现也不会强制所声明的维度数。一个特定元素类型的数组全部被当作是相同的类型，而不论其尺寸或维度数。因此，在\ ``CREATE TABLE``\ 中声明数组的尺寸或维度数仅仅只是文档而已，它并不影响运行时的行为。 另一种符合SQL标准的语法是使用关键词\ ``ARRAY``\ ，可以用来定义一维数组。\ ``pay_by_quarter``\ 可以这样定义： .. code:: programlisting pay_by_quarter integer ARRAY[4], 或者，不指定数组尺寸： .. code:: programlisting pay_by_quarter integer ARRAY, 但是和前面一样，PostgreSQL在任何情况下都不会强制尺寸限制。 .. container:: sect2 :name: ARRAYS-INPUT .. container:: titlepage .. container:: .. container:: .. rubric:: 数组值输入 :name: 数组值输入 :class: title 要把一个数组值写成一个文字常数，将元素值用花括号包围并用逗号分隔（如果你懂C，这和初始化结构的C语法没什么两样）。在任意元素值周围可以使用双引号，并且在元素值包含逗号或花括号时必须这样做（更多细节如下所示）。因此，一个数组常量的一般格式如下： .. code:: synopsis '{ val1 delim val2 delim ... }' 这里\ ``delim``\ 是类型的定界符，记录在类型的\ ``pg_type``\ 项中。在PostgreSQL发行提供的标准数据类型中，所有的都使用一个逗号（\ ``,``\ ），除了类型\ ``box``\ 使用一个分号（\ ``;``\ ）。每个\ ``val``\ 可以是数组元素类型的一个常量，也可以是一个子数组。一个数组常量的例子是： .. code:: programlisting '{{1,2,3},{4,5,6},{7,8,9}}' 该常量是一个二维的，3乘3数组，它由3个整数子数组构成。 要设置一个数组常量的一个元素为NULL，在该元素值处写\ ``NULL``\ （任何\ ``NULL``\ 的大写或小写变体都有效）。如果你需要一个真正的字符串值“NULL”，你必须在它两边放上双引号。 （这些种类的数组常数实际是\ `词法结构 `__\ 中讨论的一般类型常量的一种特殊形式。常数最初被当做一个字符串，然后被传给数组的输入转换例程。有必要时可能需要一个显式的类型指定。） 现在我们可以展示一些\ ``INSERT``\ 语句： .. code:: programlisting 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"}}'); 前两个插入的结果看起来像这样： .. code:: programlisting 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) 多维数组的每一维都必须有相匹配的长度。不匹配会造成错误，例如： .. code:: programlisting INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"meeting"}}'); ERROR: multidimensional arrays must have array expressions with matching dimensions ``ARRAY``\ 构造器语法也可以被用于： .. code:: programlisting 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``\ 构造器语法的详细讨论请见\ `值表达式 `__\ 。 .. container:: sect2 :name: ARRAYS-ACCESSING .. container:: titlepage .. container:: .. container:: .. rubric:: 访问数组 :name: 访问数组 :class: title 现在，我们可以在该表上运行一些查询。首先，我们展示如何访问一个数组中的一个元素。下面的查询检索在第二季度工资发生变化的雇员的名字： .. code:: programlisting 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``]。 下面的查询检索所有员工第三季度的工资： .. code:: programlisting SELECT pay_by_quarter[3] FROM sal_emp; pay_by_quarter ---------------- 10000 25000 (2 rows) 我们也可以访问一个数组的任意矩形切片或者子数组。一个数组切片可以通过在一个或多个数组维度上指定\ ``下界``: ``上界``\ 来定义例如，下面的查询检索Bill在本周头两天日程中的第一项： .. code:: programlisting SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill'; schedule ------------------------ {{meeting},{training}} (1 row) 如果任何维度被写成一个切片，即包含一个冒号，那么所有的维度都被看成是切片对待。其中任何只有一个数字（无冒号）的维度被视作是从1到指定的数字。例如，下面例子中的\ ``[2]``\ 被认为是\ ``[1:2]``\ ： .. code:: programlisting 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``\ （亦可两者都省略），缺失的 边界会被数组下标的上下限所替代。例如： .. code:: programlisting 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) 如果数组本身为空或者任何一个下标表达式为空，访问数组下标表达式将会返回空值。如果下标超过了数组边界，下标表达式也会返回空值（这种情况不会抛出错误）。例如，如果\ ``schedule``\ 目前具有的维度是\ ``[1:3][1:2]``\ ，那么引用\ ``schedule[3][3]``\ 将得到NULL。相似地，使用错误的下标号引用一个数组会得到空值而不是错误。 如果数组本身或者任何一个下标表达式为空，则一个数组切片表达式也会得到空值。但是，在其他情况例如选择一个完全位于当前数组边界之外的切片时，一个切片表达式会得到一个空（零维）数组而不是空值（由于历史原因，这并不符合非切片行为）。 如果所请求的切片和数组边界重叠，那么它会被缩减为重叠的区域而不是返回空。 任何数组值的当前维度可以使用\ ``array_dims``\ 函数获得： .. code:: programlisting SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims ------------ [1:2][1:2] (1 row) ``array_dims``\ 产生一个\ ``text``\ 结果，它便于人类阅读但是不便于程序读取。 Dimensions can also be retrieved with 也可以通过\ ``array_upper``\ 和\ ``array_lower``\ 来获得维度，它们将分别返回一个指定数组的上界和下界： .. code:: programlisting SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol'; array_upper ------------- 2 (1 row) ``array_length``\ 将返回一个指定数组维度的长度： .. code:: programlisting SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol'; array_length -------------- 2 (1 row) ``cardinality``\ 返回一个数组中在所有维度上的元素总数。 这实际上是调用\ ``unnest``\ 将会得到的行数： .. code:: programlisting SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol'; cardinality ------------- 4 (1 row) .. container:: sect2 :name: ARRAYS-MODIFYING .. container:: titlepage .. container:: .. container:: .. rubric:: 修改数组 :name: 修改数组 :class: title 一个数组值可以被整个替换： .. code:: programlisting UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol'; 或者使用\ ``ARRAY``\ 表达式语法： .. code:: programlisting UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; 一个数组也可以在一个元素上被更新： .. code:: programlisting UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill'; 或者在一个切片上被更新： .. code:: programlisting UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; 也可以使用省略\ ``lower-bound``\ 或者 ``upper-bound``\ 的切片语法，但是只能用于 更新一个不是 NULL 或者零维的数组值（否则无法替换现有的下标界线）。 一个已存储的数组值可以被通过为其还不存在的元素赋值来扩大之。任何位于之前已存在的元素和新元素之间的位置都将被空值填充。例如，如果数组\ ``myarray``\ 目前有4个元素，在用一个更新对\ ``myarray[6]``\ 赋值后它将有6个元素，其中\ ``myarray[5]``\ 为空值。目前，采用这种方式扩大数组只允许使用在一维数组上。 带下标的赋值方式允许创建下标不是从1开始的数组。例如，我们可以为\ ``myarray[-2:7]``\ 赋值来创建一个下标值从-2到7的数组。 新的数组值也可以通过串接操作符\ ``||``\ 构建： .. code:: programlisting 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``\ 维数组。 当一个单独的元素被加入到一个一维数组的开头或末尾时，其结果是一个和数组操作数具有相同下界下标的新数组。例如： .. code:: programlisting 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) 当两个具有相同维度数的数组被串接时，其结果保留左操作数的外维度的下界下标。结果将是一个数组，它由左操作数的每一个元素以及紧接着的右操作数的每一个元素。例如： .. code:: programlisting 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``\ 维数组外维度的一个元素。例如： .. code:: programlisting 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``\ 支持多维数组。 一些例子： .. code:: programlisting 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}} 在简单的情况中，上面讨论的串接操作符比直接使用这些函数更好。不过，由于 串接操作符需要服务于所有三种情况，所以它的负担比较重，在有些情况下使用 这些函数之一有助于避免混淆。例如： .. code:: programlisting SELECT ARRAY[1, 2] || '{3, 4}'; -- 没有指定类型的文字被当做一个数组 ?column? ----------- {1,2,3,4} SELECT ARRAY[1, 2] || '7'; -- 这个也是 ERROR: malformed array literal: "7" SELECT ARRAY[1, 2] || NULL; -- 未修饰的 NULL 也是如此 ?column? ---------- {1,2} (1 row) SELECT array_append(ARRAY[1, 2], NULL); -- 这可能才是想要的意思 array_append -------------- {1,2,NULL} 在上面的例子中，解析器看到在串接操作符的一遍看到了一个整数数组，并且在 另一边看到了一个未确定类型的常量。它用来决定该常量类型的启发式规则是假 定它和该操作符的另一个输入具有相同的类型 — 在这种情况中是整数数 组。因此串接操作符表示\ ``array_cat``\ 而不是 ``array_append``\ 。如果这样做是错误的选择，它可以通过将该常 量造型成数组的元素类型来修复。但是显式地使用\ ``array_append`` 可能是一种最好的方案。 .. container:: sect2 :name: ARRAYS-SEARCHING .. container:: titlepage .. container:: .. container:: .. rubric:: 在数组中搜索 :name: 在数组中搜索 :class: title 要在一个数组中搜索一个值，每一个值都必须被检查。这可以手动完成，但是我们必须知道数组的尺寸。例如： .. code:: programlisting 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; 但是这对于大型数组来说太过冗长，且在数组尺寸未知时无法使用。一种可选的方法可见\ `行和数组比较 `__\ 。上面的查询可以被替换为： .. code:: programlisting SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); 此外，我们还可以查找所有元素值都为10000的数组所在的行： .. code:: programlisting SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); 另外，\ ``generate_subscripts``\ 函数也可以用来完成类似的查找。例如： .. code:: programlisting 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; 该函数的描述见\ `集合返回函数 `__\ 。 我们也可以使用\ ``&&``\ 操作符来搜索一个数组，它会检查左操作数是否与右操作数重叠。例如： .. code:: programlisting SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000]; 该操作符和其他数组操作符的进一步描述请见\ `数组函数 `__\ 。如\ `索引类型 `__\ 所述，它可以使用一个合适的索引来提速。 你也可以使用\ ``array_position``\ 和\ ``array_positions``\ 在一个 数组中搜索特定值。前者返回值在数组中第一次出现的位置的下标。后者返回一个数组， 其中有该值在数组中的所有出现位置的下标。例如： .. code:: programlisting SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); array_positions ----------------- 2 SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1); array_positions ----------------- {1,4,8} .. tip:: .. rubric:: 提示 :name: 提示 :class: title 数组不是集合，在其中搜索指定数组元素可能是数据设计失误的表现。考虑使用一个独立的表来替代，其中每一行都对应于一个数组元素。这将更有利于搜索，并且对于大量元素的可扩展性更好。 .. container:: sect2 :name: ARRAYS-IO .. container:: titlepage .. container:: .. container:: .. rubric:: 数组输入和输出语法 :name: 数组输入和输出语法 :class: title 一个数组值的外部文本表现由根据数组元素类型的I/O转换规则解释的项构成，并在其上加上修饰用于指示数组结构。修饰包括数组值周围的花括号（\ ``{``\ 和\ ``}``\ ）以及相邻项之间的定界字符。定界字符通常是一个逗号（\ ``,``\ ），但是也可能是别的：它由数组元素类型的\ ``typdelim``\ 设置决定。在PostgreSQL发行版提供的标准数据类型中，除了\ ``box``\ 类型使用分号（\ ``;``\ ）之外，其他都是用逗号。在一个多维数组中，每一个维度（行、平面、方体等）都有其自己的花括号层次，且同层的被花括号限定的相邻实体之间也必须有定界符。 如果元素值是空字符串、包含花括号、包含定界字符、包含双引号、包含反斜线、包含空白或者匹配词\ ``NULL``\ ，数组输出例程将在元素值周围放上双引号。嵌在元素值中的双引号以及反斜线将被反斜线转义。对于数字数据类型可以安全地假设双引号绝不会出现，但是对于文本数据类型我们必须准备好处理可能出现亦可能不出现的引号。 默认情况下，一个数组的一个维度的下界索引值被设置为1。要表示具有其他下界的数组，数组下标的范围应在填充数组内容之前被显式地指定好。这种修饰包括在每个数组维度上下界周围的方括号（\ ``[]``\ ），以及上下界之间的一个冒号（\ ``:``\ ）定界符。数组维度修饰后面要跟一个等号（\ ``=``\ ）。例如： .. code:: programlisting 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 `__\ 配置参数设置为\ ``off``\ 来阻止将\ ``NULL``\ 识别为NULL。 如前所示，在写一个数组值时我们可以在任何单独数组元素周围使用引号。如果元素值可能混淆数组值分析器时，我们\ *必须* 这样做。例如，包含花括号、逗号（或者数据类型的定界符）、双引号、反斜线或首尾有空白的元素必须使用双引号。空字符串和匹配单词\ ``NULL``\ 的字符串也必须使用双引号。要把一个双引号或反斜线放在一个使用了双引号的数组元素值中，需要在它前面放一个反斜线。作为一种选择，我们可以免去使用引号而使用反斜线转义的方式来保护可能被认为是数组语法的所有数据字符。 我们可以在左括号前面或右括号后面增加空白。我们也可以在任何单独的项之前或之后加上空白。在所有这些情况中空白将被忽略。但是，在被使用了双引号的元素中的空白以及周围有其他非空白字符的空白不会被忽略。 .. tip:: .. rubric:: 提示 :name: 提示-1 :class: title 在SQL命令中写数组值时，\ ``ARRAY``\ 构造器语法（见\ `值表达式 `__\ ）常常比数组文字语法要更容易使用。在\ ``ARRAY``\ 中，单独的元素值可以使用不属于数组成员时的方式来书写。