Postgresql 中文操作指南
8.15. Arrays #
PostgreSQL 允许将表的列定义为可变长度多维数组。可以创建任何内置或用户定义的基本类型、枚举类型、复合类型、范围类型或域的数组。
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
8.15.1. Declaration of Array Types #
为了说明数组类型的用法，我们创建了此表：
To illustrate the use of array types, we create this table:
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);如所示，数组数据类型通过将方括号 ([]) 附加到数组元素的数据类型名称来命名。上面的命令将创建一个名为 sal_emp 的表，该表具有类型为 text (name) 的列，它是类型为 integer (pay_by_quarter) 的一维数组，表示按季度划分的员工工资，以及类型为 text (schedule) 的二维数组，表示员工的每周工作时间表。
As shown, an array data type is named by appending square brackets ([]) to the data type name of the array elements. The above command will create a table named sal_emp with a column of type text (name), a one-dimensional array of type integer (pay_by_quarter), which represents the employee’s salary by quarter, and a two-dimensional array of text (schedule), which represents the employee’s weekly schedule.
CREATE TABLE 的语法允许指定数组的确切大小，例如：
The syntax for CREATE TABLE allows the exact size of arrays to be specified, for example:
CREATE TABLE tictactoe (
squares integer[3][3]
);但是，当前实现忽略任何提供的数组大小限制，即行为与未指定长度的数组相同。
However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.
当前实现也不强制执行声明的维度数。特定元素类型的数组都视为相同类型，而与大小或维度数无关。因此，在 CREATE TABLE 中声明数组大小或维度数仅仅是文档；它不影响运行时行为。
The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.
另一种语法符合 SQL 标准，它使用关键字 ARRAY，可用于一维数组。pay_by_quarter 可以定义为：
An alternative syntax, which conforms to the SQL standard by using the keyword ARRAY, can be used for one-dimensional arrays. pay_by_quarter could have been defined as:
pay_by_quarter integer ARRAY[4],或者，如果要指定数组大小：
Or, if no array size is to be specified:
pay_by_quarter integer ARRAY,然而，与之前一样，PostgreSQL 不会在任何情况下强制执行大小限制。
As before, however, PostgreSQL does not enforce the size restriction in any case.
8.15.2. Array Value Input #
要将一个数组值写入文学常量，用花括号括住元素值，并用逗号分隔它们。（如果你了解 C，这与用于初始化结构的 C 语法并无不同。）你可以在任何元素值周围加上双引号，如果它包含逗号或花括号，则必须这样做。（更多详细信息如下。）因此，数组常量的通用格式如下：
To write an array value as a literal constant, enclose the element values within curly braces and separate them by commas. (If you know C, this is not unlike the C syntax for initializing structures.) You can put double quotes around any element value, and must do so if it contains commas or curly braces. (More details appear below.) Thus, the general format of an array constant is the following:
'{ val1 delim val2 delim ... }'其中 delim 是类型的分隔符，如其 pg_type 条目中所记录。在 PostgreSQL 发行版中提供的标准数据类型中，所有这些类型都使用逗号（,），除了类型 box，它使用分号（;）。每个 val 都是数组元素类型的一个常量，或者一个子数组。数组常量的示例如下：
where delim is the delimiter character for the type, as recorded in its pg_type entry. Among the standard data types provided in the PostgreSQL distribution, all use a comma (,), except for type box which uses a semicolon (;). Each val is either a constant of the array element type, or a subarray. An example of an array constant is:
'{{1,2,3},{4,5,6},{7,8,9}}'这个常量是一个二维的 3×3 数组，包括三个整数子数组。
This constant is a two-dimensional, 3-by-3 array consisting of three subarrays of integers.
要将数组常量的元素设置为 NULL，编写 NULL 作为元素值。（NULL 的任何大小写变体都可以。）如果你想要实际字符串值“NULL”，则必须在它周围加上双引号。
To set an element of an array constant to NULL, write NULL for the element value. (Any upper- or lower-case variant of NULL will do.) If you want an actual string value “NULL”, you must put double quotes around it.
（实际上，这些类型的数组常量只是 Section 4.1.2.7 中讨论的通用类型常量的特例。常量最初作为字符串处理，并传递给数组输入转换例程。可能需要明确的类型规范。）
(These kinds of array constants are actually only a special case of the generic type constants discussed in Section 4.1.2.7. The constant is initially treated as a string and passed to the array input conversion routine. An explicit type specification might be necessary.)
现在我们可以展示一些 INSERT 语句：
Now we can show some INSERT statements:
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"}}');前两个插入的结果如下所示：
The result of the previous two inserts looks like this:
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)多维数组每个维度都必须具有匹配的范围。不匹配会导致错误，例如：
Multidimensional arrays must have matching extents for each dimension. A mismatch causes an error, for example:
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: multidimensional arrays must have array expressions with matching dimensionsARRAY 构造语法也可以使用：
The ARRAY constructor syntax can also be used:
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 常量或表达式；例如，字符串文字用单引号引起来，而不是像在数组文字中那样用双引号引起来。在 Section 4.2.12 中更详细地讨论 ARRAY 构造函数语法。
Notice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal. The ARRAY constructor syntax is discussed in more detail in Section 4.2.12.
8.15.3. Accessing Arrays #
现在，我们可以针对该表运行一些查询。首先，我们将展示如何访问数组的单个元素。此查询检索了第二季度工资发生变化的员工的姓名：
Now, we can run some queries on the table. First, we show how to access a single element of an array. This query retrieves the names of the employees whose pay changed in the second quarter:
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)数组下标数字写在方括号中。默认情况下，PostgreSQL 对数组使用基于一的编号约定，即包含 n 个元素的数组从 array[1] 开始，到 array[_n]_ 结束。
The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[_n]_.
此查询检索所有员工的第三季度工资：
This query retrieves the third quarter pay of all employees:
SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
10000
25000
(2 rows)我们还可以访问数组的任意矩形切片，或子数组。通过为一个或多个数组维度写入 lower-bound:_upper-bound_ 来表示数组切片。例如，此查询检索 Bill 计划表上本周前两天的第一个项目：
We can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing lower-bound_:_upper-bound for one or more array dimensions. For example, this query retrieves the first item on Bill’s schedule for the first two days of the week:
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)如果任何维度作为切片编写，即包含冒号，则所有维度都将作为切片处理。仅具有单个数字（没有冒号）的任何维度都将视为从 1 到指定数字。例如，[2] 被视为 [1:2]，如下例所示：
If any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example, [2] is treated as [1:2], as in this example:
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------------------------------
{{meeting,lunch},{training,presentation}}
(1 row)为了避免与非切片实例混淆，最好对所有维度使用切片语法，例如，[1:2][1:1] chứ không phải [2][1:1].
To avoid confusion with the non-slice case, it’s best to use slice syntax for all dimensions, e.g., [1:2][1:1], not [2][1:1].
可以省略切片说明符的 lower-bound 和/或 upper-bound；缺失的边界将由数组下标的下限或上限代替。例如：
It is possible to omit the lower-bound and/or upper-bound of a slice specifier; the missing bound is replaced by the lower or upper limit of the array’s subscripts. For example:
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)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.
An array subscript expression will return null if either the array itself or any of the subscript expressions are null. Also, null is returned if a subscript is outside the array bounds (this case does not raise an error). For example, if schedule currently has the dimensions [1:3][1:2] then referencing schedule[3][3] yields NULL. Similarly, an array reference with the wrong number of subscripts yields a null rather than an error.
同样，如果数组本身或任何下标表达式为 null，数组切片表达式也产生 null。但是，在其他情况下，例如选择完全超出当前数组边界的数组切片，切片表达式会产生一个空数组（零维）而不是 null。（这不符合非切片行为，并且出于历史原因这样做。）如果请求的切片部分重叠数组边界，则它会静默地缩减为仅重叠区域，而不是返回 null。
An array slice expression likewise yields null if the array itself or any of the subscript expressions are null. However, in other cases such as selecting an array slice that is completely outside the current array bounds, a slice expression yields an empty (zero-dimensional) array instead of null. (This does not match non-slice behavior and is done for historical reasons.) If the requested slice partially overlaps the array bounds, then it is silently reduced to just the overlapping region instead of returning null.
可以使用 array_dims 函数获取任何数组值的当前维度：
The current dimensions of any array value can be retrieved with the array_dims function:
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 获取维度，它们分别返回指定数组维度的上限和下限：
array_dims produces a text result, which is convenient for people to read but perhaps inconvenient for programs. Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower bound of a specified array dimension, respectively:
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_upper
-------------
2
(1 row)array_length 将返回指定数组维度的长度：
array_length will return the length of a specified array dimension:
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_length
--------------
2
(1 row)cardinality 会返回数组中所有维度上的元素总数。它实际上是调用 unnest 会产生的行数：
cardinality returns the total number of elements in an array across all dimensions. It is effectively the number of rows a call to unnest would yield:
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
cardinality
-------------
4
(1 row)8.15.4. Modifying Arrays #
可以完全替换数组值：
An array value can be replaced completely:
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';或使用 ARRAY 表达式语法：
or using the ARRAY expression syntax:
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';也可以更新单个元素的数组：
An array can also be updated at a single element:
UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';或更新切片中的元素：
or updated in a slice:
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';也可以使用省略 lower-bound 和/或 upper-bound 的切片语法，但前提是更新的数组值不为 NULL 或零维（否则，没有现有下标限制可以替代）。
The slice syntaxes with omitted lower-bound and/or upper-bound can be used too, but only when updating an array value that is not NULL or zero-dimensional (otherwise, there is no existing subscript limit to substitute).
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.
A stored array value can be enlarged by assigning to elements not already present. Any positions between those previously present and the newly assigned elements will be filled with nulls. For example, if array myarray currently has 4 elements, it will have six elements after an update that assigns to myarray[6]; myarray[5] will contain null. Currently, enlargement in this fashion is only allowed for one-dimensional arrays, not multidimensional arrays.
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.
Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values from -2 to 7.
还可以使用连接运算符 || 构建新的数组值：
New array values can also be constructed using the concatenation operator, ||:
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 维数组。
The concatenation operator allows a single element to be pushed onto the beginning or end of a one-dimensional array. It also accepts two N-dimensional arrays, or an N-dimensional and an N+1-dimensional array.
当一个元素推送到一维数组的开头或结尾时，结果将是一个数组，其下限下标与数组操作数相同。例如：
When a single element is pushed onto either the beginning or end of a one-dimensional array, the result is an array with the same lower bound subscript as the array operand. For example:
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)当两个具有相同维数的数组连接在一起时，结果将保留左手操作数外部维度的下限下标。结果是一个数组，包含左手操作数的所有元素，后跟右手操作数的所有元素。例如：
When two arrays with an equal number of dimensions are concatenated, the result retains the lower bound subscript of the left-hand operand’s outer dimension. The result is an array comprising every element of the left-hand operand followed by every element of the right-hand operand. For example:
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 四维数组外部维度的元素。例如：
When an N-dimensional array is pushed onto the beginning or end of an N+1-dimensional array, the result is analogous to the element-array case above. Each N-dimensional sub-array is essentially an element of the N+1-dimensional array’s outer dimension. For example:
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 支持多维数组。一些示例：
An array can also be constructed by using the functions array_prepend, array_append, or array_cat. The first two only support one-dimensional arrays, but array_cat supports multidimensional arrays. Some examples:
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}}在简单的情况下，首选上述连接运算符，而不是直接使用这些函数。不过，由于连接运算符被重载以服务所有三种情况，因此在有些情况下使用其中的一个函数有助于消除歧义。例如考虑：
In simple cases, the concatenation operator discussed above is preferred over direct use of these functions. However, because the concatenation operator is overloaded to serve all three cases, there are situations where use of one of the functions is helpful to avoid ambiguity. For example consider:
SELECT ARRAY[1, 2] || '{3, 4}'; -- the untyped literal is taken as an array
?column?
-----------
{1,2,3,4}
SELECT ARRAY[1, 2] || '7'; -- so is this one
ERROR: malformed array literal: "7"
SELECT ARRAY[1, 2] || NULL; -- so is an undecorated NULL
?column?
----------
{1,2}
(1 row)
SELECT array_append(ARRAY[1, 2], NULL); -- this might have been meant
array_append
--------------
{1,2,NULL}在上述示例中，解析器在连接运算符的一侧看到一个整数数组，另一侧看到一个类型不确定的常量。它用于解析常量类型的方法是假设它与运算符的另一个输入类型相同，在本例中为整数数组。因此，连接运算符假定表示 array_cat，而不是 array_append。当这是错误的选择时，可以通过将常量强制转换为数组的元素类型来修复；但显式使用 array_append 可能是更好的解决方案。
In the examples above, the parser sees an integer array on one side of the concatenation operator, and a constant of undetermined type on the other. The heuristic it uses to resolve the constant’s type is to assume it’s of the same type as the operator’s other input — in this case, integer array. So the concatenation operator is presumed to represent array_cat, not array_append. When that’s the wrong choice, it could be fixed by casting the constant to the array’s element type; but explicit use of array_append might be a preferable solution.
8.15.5. Searching in Arrays #
要搜索数组中的值，必须检查每个值。如果您知道数组的大小，则可以手动执行此操作。例如：
To search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array. For example:
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;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:
However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. An alternative method is described in Section 9.24. The above query could be replaced by:
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);此外，您可以通过以下方式找到其中数组的所有值都等于 10000 的行：
In addition, you can find rows where the array has all values equal to 10000 with:
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);或者，可以使用 generate_subscripts 函数。例如：
Alternatively, the generate_subscripts function can be used. For example:
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;Chức năng này được mô tả trong Table 9.66.
This function is described in Table 9.66.
您还可以使用检查左操作数是否与右操作数重叠的 && 运算符搜索数组。例如：
You can also search an array using the && operator, which checks whether the left operand overlaps with the right operand. For instance:
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];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.
This and other array operators are further described in Section 9.19. It can be accelerated by an appropriate index, as described in Section 11.2.
您还可以使用 array_position 和 array_positions 函数在数组中搜索指定的值。前者返回数组中值第一次出现时的下标；后者返回数组中值的出现下标的数组。例如：
You can also search for specific values in an array using the array_position and array_positions functions. The former returns the subscript of the first occurrence of a value in an array; the latter returns an array with the subscripts of all occurrences of the value in the array. For example:
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_position
----------------
2
(1 row)
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
array_positions
-----------------
{1,4,8}
(1 row)Tip
数组不是集合；搜索特定的数组元素可能是数据库设计错误的标志。考虑为每个数组元素行创建一个单独的表。这将更容易搜索，并且对于大量元素来说可能具有更好的可扩展性。
Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.
8.15.6. Array Input and Output Syntax #
数组值的外部文本表示由按照数组元素类型的 I/O 转换规则解释的项目以及指示数组结构的修饰组成。该修饰由数组值周围的大括号 ({_和}_) 加上邻接项之间的分隔符字符组成。分隔符字符通常是逗号 (,)，但也可以是其他字符：由数组元素类型的 typdelim 设置确定。在 PostgreSQL 分发中提供的标准数据类型中，所有类型都使用逗号，除了类型 box，它使用分号 (;)。在多维数组中，每个维度（行、平面、立方体等）都有自己级别的花括号，并且必须在同一级别的相邻花括号实体之间编写分隔符。
The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array’s element type, plus decoration that indicates the array structure. The decoration consists of curly braces ({ and }) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (,) but can be something else: it is determined by the typdelim setting for the array’s element type. Among the standard data types provided in the PostgreSQL distribution, all use a comma, except for type box, which uses a semicolon (;). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.
如果元素值为空字符串、包含花括号、分隔符字符、双引号、反斜杠或空格，或者与单词 NULL 匹配，数组输出例程将在元素值周围加上双引号。嵌入在元素值中的双引号和反斜杠将用反斜杠转义。对于数值数据类型，可以安全地假设永远不会出现双引号，但对于文本数据类型，应该准备好应对存在或不存在引号的情况。
The array output routine will put double quotes around element values if they are empty strings, contain curly braces, delimiter characters, double quotes, backslashes, or white space, or match the word NULL. Double quotes and backslashes embedded in element values will be backslash-escaped. For numeric data types it is safe to assume that double quotes will never appear, but for textual data types one should be prepared to cope with either the presence or absence of quotes.
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ụ:
By default, the lower bound index value of an array’s dimensions is set to one. To represent arrays with other lower bounds, the array subscript ranges can be specified explicitly before writing the array contents. This decoration consists of square brackets ([]) around each array dimension’s lower and upper bounds, with a colon (:) delimiter character in between. The array dimension decoration is followed by an equal sign (=). For example:
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 不同时，数组输出例程才会在其结果中包含显式维度。
The array output routine will include explicit dimensions in its result only when there are one or more lower bounds different from one.
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.
If the value written for an element is NULL (in any case variant), the element is taken to be NULL. The presence of any quotes or backslashes disables this and allows the literal string value “NULL” to be entered. Also, for backward compatibility with pre-8.2 versions of PostgreSQL, the array_nulls configuration parameter can be turned off to suppress recognition of NULL as a NULL.
如前所示，当写入数组值时，您可以在任何单个数组元素周围使用双引号。如果您这样做，否则数组值解析器将混淆元素值。例如，必须对包含大括号、逗号（或数据类型的分隔符字符）、双引号、反斜杠或前导或尾随空格的元素加上双引号。空字符串和与单词 NULL 匹配的字符串也必须加上引号。要在带引号的数组元素值中放置双引号或反斜杠，请在它们之前加上反斜杠。或者，您可以避免使用引号和反斜杠来保护所有可能被视为数组语法的字符。
As shown previously, when writing an array value you can use double quotes around any individual array element. You must do so if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or the data type’s delimiter character), double quotes, backslashes, or leading or trailing whitespace must be double-quoted. Empty strings and strings matching the word NULL must be quoted, too. To put a double quote or backslash in a quoted array element value, precede it with a backslash. Alternatively, you can avoid quotes and use backslash-escaping to protect all data characters that would otherwise be taken as array syntax.
可以在左大括号前或右大括号后添加空格。还可以在任何单个项目字符串前或后添加空格。在所有这些情况下，都会忽略空格。然而，双引号元素中的空格或在元素两侧被非空格字符包围的空格不会被忽略。
You can add whitespace before a left brace or after a right brace. You can also add whitespace before or after any individual item string. In all of these cases the whitespace will be ignored. However, whitespace within double-quoted elements, or surrounded on both sides by non-whitespace characters of an element, is not ignored.
Tip
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.
The ARRAY constructor syntax (see Section 4.2.12) is often easier to work with than the array-literal syntax when writing array values in SQL commands. In ARRAY, individual element values are written the same way they would be written when not members of an array.