Postgresql数据库的Array类型字段官方文档说明

8.15 Arrays Type

PostgreSQL允许将表的列定义为可变长多维数组。可以创建任何内置或用户定义的基类型、枚举类型、组合类型、范围类型或域的数组。

8.15.1. 数组类型的声明

为了说明数组类型的用法，我们创建这个表:

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

如所示，数组数据类型是通过在数组元素的数据类型名称后附加方括号([])来命名的。上面的命令将创建一个名为sal_emp的表，其中一个列类型为text (name)，一个类型为integer的一维数组(pay_by_quarter)，表示员工按季度的工资，还有一个二维文本数组(schedule)，表示员工的每周时间表。

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语言，这与C语言中初始化结构的语法没有什么不同。)您可以在任何元素值周围加上双引号，如果它包含逗号或花括号，则必须这样做。(详情见下文。)因此，数组常量的一般格式如下:

'{ val1 delim val2 delim ... }'

delim是类型的分隔符，记录在pg_type条目中。在PostgreSQL发行版中提供的标准数据类型中，除了类型框使用分号(;)外，所有数据类型都使用逗号(，)。每个val要么是数组元素类型的常量，要么是子数组。数组常量的一个例子是:

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

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

要将数组常量的一个元素设置为NULL，请将元素值写入NULL。(NULL的任何大写或小写变体都可以。)如果你想要一个实际的字符串值“NULL”，你必须在它周围加上双引号。

(这些类型的数组常量实际上只是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;

多维数组必须为每个维具有匹配的区段。不匹配会导致错误，例如:

INSERT INTO sal_emp
 VALUES ('Bill',
 '{10000, 10000, 10000, 10000}',
 '{{"meeting", "lunch"}, {"meeting"}}');

错误:多维数组必须有匹配维度的数组表达式

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构造函数语法将在第4.2.12节中详细讨论。

8.15.3. 访问数组

现在，我们可以在表上运行一些查询。首先，我们展示如何访问数组中的单个元素。该查询检索在第二季度工资发生变化的员工的姓名:

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

数组下标数字写在方括号内。默认情况下，PostgreSQL对数组使用一种基于1个节点的编号约定，即一个包含n个元素的数组以数组[1]开始，以数组[n]结束。

此查询检索所有员工的第三季度工资:

SELECT pay_by_quarter[3] FROM sal_emp;

我们还可以访问数组或子数组的任意矩形切片。数组切片可以用一个或多个数组维度的下界:上界来表示。例如，该查询检索Bill一周前两天的日程安排中的第一项:

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

如果任何维度被写成切片，即包含冒号，那么所有维度都被视为切片。任何只有一个数字(没有冒号)的维度都被视为从1到指定的数字。例如，[2]被视为[1:2]，如下例所示:

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

为了避免与非切片情况混淆，最好对所有维度使用切片语法，例如，[1:2][1:1]，而不是[2][1:1]。
可以省略切片说明符的下界和/或上界;缺失的边界将被数组下标的下限或上限取代。例如:

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

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

如果数组本身或任何一个下标表达式为空，则数组下标表达式将返回null。此外，如果下标在数组边界之外，则返回null(这种情况不会引发错误)。例如，如果调度当前的维度为[1:3][1:2]，那么引用调度[3][3]将产生NULL。类似地，具有错误下标数量的数组引用将产生null而不是错误。

同样，如果数组本身或任何下标表达式为空，则数组切片表达式的结果为空。然而，在其他情况下，例如选择完全超出当前数组边界的数组切片，切片表达式将生成空(零维)数组而不是空数组。(这与非切片行为不匹配，是出于历史原因。)如果请求的片部分重叠数组边界，那么它将被无声地缩减为重叠区域，而不是返回null。

任何数组值的当前维数都可以用array_dims函数检索:

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

array_dim生成一个文本结果，方便人们阅读，但可能对程序不方便。维度也可以使用array_upper和array_lower来检索，它们分别返回指定数组维度的上界和下界:

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

Array_length返回指定数组维数的长度:

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

基数返回数组中所有维度上的元素总数。它实际上是调用unnest将产生的行数:

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

8.15.4. Modifying Arrays 修改数组

数组值可以被完全替换:

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或零维数组值时使用(否则，没有现有的下标限制可以替换)。

存储的数组值可以通过赋值给不存在的元素来扩大。之前存在的元素之间的任何位置都为，新分配的元素将被null填充。对于考试ple，如果数组myarray当前有4个元素，则更新后将有6个元素赋值给myarray[6];Myarray[5]将包含null。目前，这种方式的扩展只允许用于一维数组，不允许用于多维数组。

下标赋值允许创建不使用基于一的下标的数组。例如，可以给myarray[-2:7]赋值来创建下标值为-2到7的数组。

新的数组值也可以使用连接操作符||来构造:

SELECT ARRAY[1,2] || ARRAY[3,4];

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];

连接操作符允许将单个元素压入到一个一维数组的开头或结尾。它还接受两个N维数组，或者一个N维数组和一个N 1维数组。

当单个元素被压入一维数组的开头或结尾时，结果是一个具有与数组操作数相同下界下标的数组。例如:

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);

SELECT array_dims(ARRAY[1,2] || 3);

当两个维数相等的数组连接时，结果将保留左操作数外层维的下界下标。其结果是一个包含左操作数的每个元素和右操作数的每个元素的数组。例如:

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);

当一个N维数组被压入到一个N 1维数组的开头或结尾时，结果类似于上面的元素数组情况。每个N维子数组本质上是N 1维数组外维的一个元素。例如:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);

数组还可以使用函数array_prepend、array_append或array_cat来构造。前两个只支持一维数组，但array_cat支持多维数组。一些例子:

SELECT array_prepend(1, ARRAY[2,3]);

SELECT array_append(ARRAY[1,2], 3);

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);

在简单的情况下，优先使用上面讨论的连接操作符，而不是直接使用这些函数。但是，由于连接运算符重载以满足所有三种情况，因此在某些情况下，使用其中一个函数有助于避免歧义。例如考虑:

SELECT ARRAY[1, 2] || '{3, 4}'; -- the untyped literal is taken as an array

SELECT ARRAY[1, 2] || '7'; -- so is this one

ERROR: malformed array literal: “7”

SELECT ARRAY[1, 2] || NULL; -- so is an undecorated NULL

SELECT array_append(ARRAY[1, 2], NULL); -- this might have been meant

在上面的示例中，解析器在连接操作符的一侧看到一个整数数组，在另一侧看到一个类型未定的常量。它用来解析常量类型的启发式方法是假设它与运算符的其他输入类型相同——在本例中是整数数组。因此，连接操作符被假定为表示array_cat，而不是array_append。当这是错误的选择时，可以通过将常量强制转换为数组的元素类型来修复;但是显式使用array_append可能是一个更好的解决方案。

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;

然而，对于大型数组，这很快就变得乏味，如果数组的大小未知，则没有帮助。另一种方法在9.24节中描述。上面的查询可以替换为:

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;

该函数如表9.65所示。
还可以使用&&操作符搜索数组，该操作符检查左操作数与右操作数是否重叠。例如:

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

This和其他数组操作符将在9.19节中进一步描述。可以通过适当的索引加速，如第11节所述

还可以使用array_position和array_positions函数在数组中搜索特定的值。前者返回数组中某个值第一次出现的下标;后者返回一个数组，其中包含数组中出现的所有值的下标。例如:

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'],'mon');

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);

数组不是集合;搜索特定的数组元素可能是数据库设计错误的标志。考虑使用一个单独的表，其中每个项目都有一行，这将是一个数组元素。这将更容易搜索，并且可能对大量元素有更好的伸缩性。

8.15.6. 数组输入和输出语法

数组值的外部文本表示形式包括根据数组元素类型的I/O转换规则解释的项，以及指示数组结构的修饰。修饰由数组值周围的花括号({和})和相邻项之间的分隔符组成。分隔符通常是逗号(，)，但也可以是其他字符:它由数组元素类型的typdelim设置决定。在PostgreSQL发行版中提供的标准数据类型中，除了type 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;

数组输出例程只有在存在一个或多个不同的下界时，才会在其结果中包含显式的维度。

如果为一个元素写的值为NULL(在任何情况下都是变量)，则该元素被取为NULL。任何引号或反斜杠的出现都将禁用此功能，并允许输入字面字符串值“NULL”。另外，为了向后兼容8.2之前版本的PostgreSQL，可以关闭array_nulls配置参数，以抑制将NULL识别为NULL。

如前所述，在编写数组值时，可以在任何单独的数组元素周围使用双引号。如果元素值会使数组值解析器混淆，则必须这样做。例如，包含花括号、逗号(或数据类型的分隔符)、双引号、反斜杠或前导或尾随空格的元素必须使用双引号。空字符串和匹配NULL的字符串也必须加引号。若要在带引号的数组元素值中放入双引号或反斜杠，请在其前面加上反斜杠。或者，您可以避免使用引号并使用反斜杠转义来保护所有数据字符，否则这些数据字符将被作为数组语法。

可以在左大括号之前或右大括号之后添加空格。您还可以在任何单独的项目字符串之前或之后添加空白。在所有这些情况下，空格都将被忽略。但是，双引号元素内的空格，或元素两侧被非空格字符包围的空格不会被忽略。

在SQL命令中编写数组值时，ARRAY构造函数语法(参见4.2.12节)通常比数组文字语法更容易使用。在ARRAY中，单个元素值的书写方式与它们不是数组成员时的书写方式相同。

这篇好文章是转载于：学新通技术网