Postgresql 中文操作指南

为了说明数组类型的用法，我们创建了此表：

如所示，数组数据类型通过将方括号 ([]) 附加到数组元素的数据类型名称来命名。上面的命令将创建一个名为 sal_emp 的表，该表具有类型为 text (name) 的列，它是类型为 integer (pay_by_quarter) 的一维数组，表示按季度划分的员工工资，以及类型为 text (schedule) 的二维数组，表示员工的每周工作时间表。

CREATE TABLE 的语法允许指定数组的确切大小，例如：

但是，当前实现忽略任何提供的数组大小限制，即行为与未指定长度的数组相同。

当前实现也不强制执行声明的维度数。特定元素类型的数组都视为相同类型，而与大小或维度数无关。因此，在 CREATE TABLE 中声明数组大小或维度数仅仅是文档；它不影响运行时行为。

另一种语法符合 SQL 标准，它使用关键字 ARRAY，可用于一维数组。pay_by_quarter 可以定义为：

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

然而，与之前一样，PostgreSQL 不会在任何情况下强制执行大小限制。

要将一个数组值写入文学常量，用花括号括住元素值，并用逗号分隔它们。（如果你了解 C，这与用于初始化结构的 C 语法并无不同。）你可以在任何元素值周围加上双引号，如果它包含逗号或花括号，则必须这样做。（更多详细信息如下。）因此，数组常量的通用格式如下：

其中 delim 是类型的分隔符，如其 pg_type 条目中所记录。在 PostgreSQL 发行版中提供的标准数据类型中，所有这些类型都使用逗号（,），除了类型 box，它使用分号（;）。每个 val 都是数组元素类型的一个常量，或者一个子数组。数组常量的示例如下：

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

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

（实际上，这些类型的数组常量只是 Section 4.1.2.7 中讨论的通用类型常量的特例。常量最初作为字符串处理，并传递给数组输入转换例程。可能需要明确的类型规范。）

现在我们可以展示一些 INSERT 语句：

前两个插入的结果如下所示：

多维数组每个维度都必须具有匹配的范围。不匹配会导致错误，例如：

ARRAY 构造语法也可以使用：

请注意，数组元素是普通的 SQL 常量或表达式；例如，字符串文字用单引号引起来，而不是像在数组文字中那样用双引号引起来。在 Section 4.2.12 中更详细地讨论 ARRAY 构造函数语法。

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

数组下标数字写在方括号中。默认情况下，PostgreSQL 对数组使用基于一的编号约定，即包含 n 个元素的数组从 array[1] 开始，到 array[_n]_ 结束。

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

我们还可以访问数组的任意矩形切片，或子数组。通过为一个或多个数组维度写入 lower-bound:_upper-bound_ 来表示数组切片。例如，此查询检索 Bill 计划表上本周前两天的第一个项目：

如果任何维度作为切片编写，即包含冒号，则所有维度都将作为切片处理。仅具有单个数字（没有冒号）的任何维度都将视为从 1 到指定数字。例如，[2] 被视为 [1:2]，如下例所示：

为了避免与非切片实例混淆，最好对所有维度使用切片语法，例如，[1:2][1:1] chứ không phải [2][1:1].

可以省略切片说明符的 lower-bound 和/或 upper-bound；缺失的边界将由数组下标的下限或上限代替。例如：

Nếu mảng đó hoặc bất kỳ biểu thức chỉ số so sánh nào có giá trị null thì biểu thức chỉ số so sánh mảng sẽ trả về null. Ngoài ra, một chỉ mục nằm ngoài phạm vi của mảng cũng trả về null (trường hợp này không tạo ra lỗi). Ví dụ: nếu schedule hiện có các kích thước [1:3][1:2] thì việc tham chiếu đến schedule[3][3] tạo ra giá trị NULL. Tương tự, một tham chiếu mảng có số lượng chỉ số không đúng sẽ tạo ra một giá trị null chứ không phải lỗi.

同样，如果数组本身或任何下标表达式为 null，数组切片表达式也产生 null。但是，在其他情况下，例如选择完全超出当前数组边界的数组切片，切片表达式会产生一个空数组（零维）而不是 null。（这不符合非切片行为，并且出于历史原因这样做。）如果请求的切片部分重叠数组边界，则它会静默地缩减为仅重叠区域，而不是返回 null。

可以使用 array_dims 函数获取任何数组值的当前维度：

array_dims 会产生一个 text 结果，方便人们阅读，但可能不方便程序。也可以使用 array_upper 和 array_lower 获取维度，它们分别返回指定数组维度的上限和下限：

array_length 将返回指定数组维度的长度：

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

可以完全替换数组值：

或使用 ARRAY 表达式语法：

也可以更新单个元素的数组：

或更新切片中的元素：

也可以使用省略 lower-bound 和/或 upper-bound 的切片语法，但前提是更新的数组值不为 NULL 或零维（否则，没有现有下标限制可以替代）。

Có thể mở rộng giá trị của mảng đã lưu trữ bằng cách gán cho các phần tử chưa có. Bất kỳ vị trí nào giữa các phần tử đã có từ trước và các phần tử vừa gán mới sẽ được điền giá trị null. Ví dụ: nếu mảng myarray hiện có 4 phần tử, thì nó sẽ có sáu phần tử sau khi cập nhật gán cho myarray[6]; myarray[5] sẽ chứa giá trị null. Hiện tại, chỉ được phép mở rộng theo kiểu này đối với các mảng một chiều chứ không phải mảng đa chiều.

Việc gán chỉ số cho phép tạo ra các mảng không sử dụng các chỉ số dựa trên một. Ví dụ: bạn có thể gán cho myarray[-2:7] để tạo một mảng với các giá trị chỉ số từ -2 đến 7.

还可以使用连接运算符 || 构建新的数组值：

连接运算符允许将单个元素推送到一维数组的开头或结尾。它还接受两个 N 维数组，或一个 N 维数组和一个 N+1 维数组。

当一个元素推送到一维数组的开头或结尾时，结果将是一个数组，其下限下标与数组操作数相同。例如：

当两个具有相同维数的数组连接在一起时，结果将保留左手操作数外部维度的下限下标。结果是一个数组，包含左手操作数的所有元素，后跟右手操作数的所有元素。例如：

当将 N 一维数组推送到 N+1 二维数组的开头或末尾时，结果类似于上面的元素数组情况。每个 N 三维子数组本质上是 N+1 四维数组外部维度的元素。例如：

一个数组还可使用 array_prepend、array_append 或 array_cat 函数构建。前两个仅支持一维数组，但 array_cat 支持多维数组。一些示例：

在简单的情况下，首选上述连接运算符，而不是直接使用这些函数。不过，由于连接运算符被重载以服务所有三种情况，因此在有些情况下使用其中的一个函数有助于消除歧义。例如考虑：

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

要搜索数组中的值，必须检查每个值。如果您知道数组的大小，则可以手动执行此操作。例如：

Tuy nhiên, điều này nhanh chóng trở nên tẻ nhạt đối với các mảng lớn và không hữu ích nếu kích thước của mảng không xác định. Một phương pháp thay thế được mô tả trong Section 9.24. Có thể thay thế truy vấn trên bằng:

此外，您可以通过以下方式找到其中数组的所有值都等于 10000 的行：

或者，可以使用 generate_subscripts 函数。例如：

Chức năng này được mô tả trong Table 9.66.

您还可以使用检查左操作数是否与右操作数重叠的 && 运算符搜索数组。例如：

Chức năng này cùng với các toán tử mảng khác được mô tả thêm trong Section 9.19. Như mô tả trong Section 11.2, có thể tăng tốc chức năng này bằng một chỉ mục thích hợp.

您还可以使用 array_position 和 array_positions 函数在数组中搜索指定的值。前者返回数组中值第一次出现时的下标；后者返回数组中值的出现下标的数组。例如：

数组不是集合；搜索特定的数组元素可能是数据库设计错误的标志。考虑为每个数组元素行创建一个单独的表。这将更容易搜索，并且对于大量元素来说可能具有更好的可扩展性。

数组值的外部文本表示由按照数组元素类型的 I/O 转换规则解释的项目以及指示数组结构的修饰组成。该修饰由数组值周围的大括号 ({_和}_) 加上邻接项之间的分隔符字符组成。分隔符字符通常是逗号 (,)，但也可以是其他字符：由数组元素类型的 typdelim 设置确定。在 PostgreSQL 分发中提供的标准数据类型中，所有类型都使用逗号，除了类型 box，它使用分号 (;)。在多维数组中，每个维度（行、平面、立方体等）都有自己级别的花括号，并且必须在同一级别的相邻花括号实体之间编写分隔符。

如果元素值为空字符串、包含花括号、分隔符字符、双引号、反斜杠或空格，或者与单词 NULL 匹配，数组输出例程将在元素值周围加上双引号。嵌入在元素值中的双引号和反斜杠将用反斜杠转义。对于数值数据类型，可以安全地假设永远不会出现双引号，但对于文本数据类型，应该准备好应对存在或不存在引号的情况。

Theo mặc định, giá trị chỉ số giới hạn dưới của các kích thước của mảng được đặt thành một. Để biểu diễn các mảng với các giới hạn dưới khác, có thể chỉ rõ phạm vi chỉ số mảng một cách rõ ràng trước khi viết nội dung mảng. Biểu diễn này bao gồm dấu ngoặc vuông ([]) xung quanh giới hạn trên và dưới của từng kích thước mảng, có ký tự phân cách là dấu hai chấm (:) ở giữa. Biểu diễn kích thước mảng theo sau bởi một dấu hiệu bằng (=). Ví dụ:

仅当有一个或多个下边界与 1 不同时，数组输出例程才会在其结果中包含显式维度。

Nếu giá trị được viết cho một phần tử là NULL (trong bất kỳ cách viết tắt nào), phần tử đó sẽ được coi là NULL. Sự hiện diện của bất kỳ dấu ngoặc kép hoặc dấu gạch chéo nào sẽ vô hiệu hóa điều này và cho phép nhập giá trị chuỗi theo nghĩa đen “NULL”. Ngoài ra, để tương thích ngược với các phiên bản PostgreSQL trước 8.2, có thể bật tham số cấu hình array_nulls off để vô hiệu hóa việc nhận dạng NULL là NULL.

如前所示，当写入数组值时，您可以在任何单个数组元素周围使用双引号。如果您这样做，否则数组值解析器将混淆元素值。例如，必须对包含大括号、逗号（或数据类型的分隔符字符）、双引号、反斜杠或前导或尾随空格的元素加上双引号。空字符串和与单词 NULL 匹配的字符串也必须加上引号。要在带引号的数组元素值中放置双引号或反斜杠，请在它们之前加上反斜杠。或者，您可以避免使用引号和反斜杠来保护所有可能被视为数组语法的字符。

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

Cú pháp cấu tử ARRAY (xem Section 4.2.12) thường dễ làm việc hơn cú pháp theo nghĩa đen của mảng khi viết các giá trị mảng trong các lệnh SQL. Trong ARRAY, các giá trị phần tử riêng lẻ được viết theo cùng một cách như khi không thuộc mảng.