数组#

OushuDB 允许将列定义成变长的多维数组。数组类型可以是任何基本类型或用户定义类型。(目前还不支持域或复合类型的数组。)

数组类型的声明#

为说明数组类型的使用，我们创建下列表：

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

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

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

CREATE TABLE tictactoe (
    squares   integer[3][3]
) format 'text';

然而，现在的实现不强制限制数组的大小-等价于未声明长度的数组。现在的实现不强制限制数组维数。特定元素类型的数组都被认为是相同的类型，不管他们的大小或者维数。因此，在CREATE TABLE 里定义数字或者维数都不影响运行时的行为。

另外还有一种语法，该方法遵循 SQL 标准，可以声明一维数组。pay_by_quarter可以定义为:

pay_by_quarter  integer ARRAY[4],

这个语句需要一个整数常量来表示数组的大小。正如前面所说，OushuDB 不强制要求数组大小限制。

备注

ROW格式的表支持数组类型；而ORC/Hudi/MAGMA格式的表都不支持数组类型。

数组值输入#

将数组写成文本常量的时候，用花括弧把数组元素括起来并且用逗号将它们分开(如果你懂 C ，那么这与初始化一个结构很像)。你可以在数组元素值周围放置单引号，但如果这个值包含逗号或者花括弧，那么就必须加上单引号(下面有更多细节)。因此，一个数组常量的常见格式如下：

'{ val1 delim val2 delim ... }'

这里的delim是该类型的分隔符，就是在该类型的 pg_type记录中指定的那个。在OushuDB 发布提供的标准数据类型里，所有类型都使用逗号(,),除了box 类型使用分号(;)之外。每个val 要么是一个数组元素类型的常量，要么是一个子数组。一个数组常量的例子如下：

'{{1,2,3},{4,5,6},{7,8,9}}'

这个常量是一个 3 乘 3 的两维数组，由三个整数子数组组成。

要将一个数组元素的值设为 NULL ，直接写上NULL即可(大小写无关)。要将一个数组元素的值设为字符串”NULL”，那么你必须加上双引号。

这种数组常量实际上只是我们在其他类型常量里讨论过的一般类型常量的一种特例。常量最初是当作字符串看待并且传递给数组输入转换器的，可能需要使用明确的类型声明。

现在我们可以展示一些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"}}');

需要注意的是ROW格式的表查询时需要切换执行器，调整后前面的两个插入的结果看起来像这样：

set new_executor = off;

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)

我们还可以使用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构造器语法在数组构造器里有更详细的讨论。

多维数组必须匹配每个维的元素数。如果不匹配将导致错误：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

访问数组#

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

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

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

数组的下标数字是写在方括弧内的。OushuDB 缺省使用以 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:1]。任意只有一个数字（没有冒号）的维数是从1开始到声明的数字为止的。例如，[2] 被认为是[1:2]，就想下面例子中一样：

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

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

如果数组本身或任何下标表达式是 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_append, array_cat函数构造。前者只支持一维数组，而array_cat 支持多维数组。一些例子：

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}}

在数组中检索#

要搜索一个数组中的数值，你必须检查该数组的每一个值。你可以手工处理(如果你知道数组尺寸)。比如：

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;

不过，对于大数组而言，这个方法很快就会让人觉得无聊，并且如果你不知道数组尺寸，那就没什么用了。另外一个方法在行和数组比较里描述。上面的查询可以用下面的代替：

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

小技巧

数组不是集合；需要像前面那样搜索数组中的特定元素通常表明你的数据库设计有问题。数组字段通常是可以分裂成独立的表。很明显表要容易搜索得多，并且在元素数目非常庞大的时候也可以更好地伸展。

数组输入输出语法#

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

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

缺省时，一个数组的某维的下标索引是设置为 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”值。

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

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

备注

请记住你在 SQL 命令里写的任何东西都将首先解释成一个字符串文本，然后才是一个数组。这样就造成你所需要的反斜杠数量翻了翻。比如，要插入一个包含反斜杠和双引号的text数组，你需要这么写：

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

字符串文本处理器去掉第一层反斜杠，然后剩下的东西到了数组数值分析器的时候将变成 {”\”,”"”}。接着，该字符串传递给text数据类型的输入过程，分别变成\和”。如果我们使用的数据类型对反斜杠也有特殊待遇，比如bytea，那么我们可能需要在命令里放多达八个反斜杠才能在存储态的数组元素中得到一个反斜杠。也可以用美元符界定(参阅美元符引用字符串常量 )来避免双份的反斜杠。

小技巧

ARRAY构造器语法(参阅数组构造器 ) 通常比数组文本语法好用些，尤其是在 SQL 命令里写数组值的时候。在ARRAY里，独立的元素值的写法和数组里没有元素时的写法一样。

OushuDB 产品文档

数组

本页目录

数组#

数组类型的声明#

数组值输入#

访问数组#

修改数组#

在数组中检索#

数组输入输出语法#