PostgreSQL-数据类型3
一、数组类型
PostgreSQL 允许将表的列定义为可变长度的多维数组。可以创建任何内置或用户定义的基本类型、枚举类型、复合类型、范围类型或域的数组。
为了说明数组类型的使用，我们创建了这个表：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
如图所示，通过将方括号 ([]) 附加到数组元素的数据类型名称来命名数组数据类型。上面的命令将创建一个名为 sal_emp 的表，其中有一列 text 类型（name），一个整数类型的一维数组（pay_by_quarter），它表示员工按季度的工资，以及一个 text 二维数组（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 在任何情况下都不会强制执行大小限制。
要将数组值写为文字常量，请将元素值括在花括号内并用逗号分隔。您可以在任何元素值周围加上双引号，如果它包含逗号或花括号，则必须这样做。因此，数组常量的一般格式如下：
'{ val1 delim val2 delim ... }'
其中 delim 是类型的分隔符，记录在其 pg_type 条目中。在 PostgreSQL 发行版中提供的标准数据类型中，除了使用分号 (😉 的类型 box 之外，所有类型都使用逗号 (,)。每个 val 要么是数组元素类型的常量，要么是子数组。数组常量的一个例子是：
'{{1,2,3},{4,5,6},{7,8,9}}'
此常量是一个二维 3×3 数组，由三个整数子数组组成。
要将数组常量的元素设置为 NULL，请将元素值写入 NULL。（NULL 的任何大写或小写变体都可以。）如果你想要一个实际的字符串值“NULL”，你必须在它周围加上双引号。
例子：
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"}}');
多维数组必须具有每个维度的匹配范围。不匹配会导致错误，例如：
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 常量或表达式；例如，字符串文字是单引号，而不是像在数组文字中那样的双引号。
数组下标数字写在方括号内。默认情况下，PostgreSQL 对数组使用从 1 开始的编号约定，即一个包含 n 个元素的数组以 array[1] 开头并以 array[n] 结尾。
SELECT pay_by_quarter[3],pay_by_quarter 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, schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
SELECT schedule, schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
如果数组本身或任何下标表达式为 null，则数组下标表达式将返回 null。此外，如果下标超出数组边界，则返回 null（这种情况不会引发错误）。例如，如果 schedule 当前具有维度 [1:3][1:2]，则引用 schedule[3][3] 会产生 NULL。类似地，下标数量错误的数组引用会产生空值而不是错误。
如果数组本身或任何下标表达式为 null，则数组切片表达式同样会产生 null。但是，在其他情况下，例如选择完全超出当前数组边界的数组切片，切片表达式会产生一个空（零维）数组而不是 null。（这与非切片行为不匹配，并且出于历史原因这样做。）如果请求的切片部分与数组边界重叠，则它会默默地减少到仅重叠区域，而不是返回 null。
select schedule,schedule[:3][1:2] from sal_emp where name='Carol';
select schedule,schedule[:3][:3] from sal_emp where name='Carol';
select schedule,schedule[3:][:3] from sal_emp where name='Carol';
select schedule,schedule[:][3:] from sal_emp where name='Carol';
select schedule,schedule[3][3:] from sal_emp where name='Carol';
select schedule,schedule[3][3] from sal_emp where name='Carol';
可以使用 array_dims 函数检索任何数组值的当前维度：
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
array_dims 产生一个文本结果，方便人们阅读，但可能不方便程序。也可以使用 array_upper 和 array_lower 检索维度，它们分别返回指定数组维度的上限和下限：
SELECT array_upper(schedule,1),array_lower(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';
数组值可以完全替换：
UPDATE sal_emp SET pay_by_quarter = '{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';
可以通过分配给尚未存在的元素来扩大存储的数组值。先前存在的元素和新分配的元素之间的任何位置都将用空值填充。例如，如果数组 myarray 当前有 4 个元素，那么在分配给 myarray[6] 的更新后它将有 6 个元素；myarray[5] 将包含空值。目前，这种方式的放大只允许用于一维数组，而不是多维数组。
下标赋值允许创建不使用从一开始的下标的数组。例如，可以分配给 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}';
SELECT ARRAY[1, 2] || '7';
SELECT array_append(ARRAY[1, 2] , '7');
SELECT ARRAY[1, 2] || NULL;
SELECT array_append(ARRAY[1, 2], NULL);
要在数组中搜索值，必须检查每个值。如果您知道数组的大小，这可以手动完成。例如：
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);
或者，可以使用 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;
您还可以使用 && 运算符搜索数组，该运算符检查左操作数是否与右操作数重叠。例如：
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
您还可以使用 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);
默认情况下，数组维度的下限索引值设置为 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 的字符串也必须被引用。要将双引号或反斜杠放在带引号的数组元素值中，请在其前面加上反斜杠。或者，您可以避免使用引号并使用反斜杠转义来保护所有否则将被视为数组语法的数据字符。
二、复合类型
复合类型表示行或记录的结构；它本质上只是一个字段名称及其数据类型的列表。PostgreSQL 允许以与使用简单类型相同的方式使用复合类型。例如，可以将表的列声明为复合类型。
下面是定义复合类型的两个简单示例：
CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
语法与 CREATE TABLE 类似，只是只能指定字段名称和类型；目前不能包含任何约束（例如 NOT NULL）。请注意，AS 关键字是必不可少的；没有它，系统会认为是另一种 CREATE TYPE 命令，你会得到奇怪的语法错误。
定义完类型后，我们可以使用它们来创建表：
CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;
SELECT price_extension(item, 10) FROM on_hand;
要将复合值写为文字常量，请将字段值括在括号内并用逗号分隔。您可以在任何字段值周围加上双引号，如果它包含逗号或括号，则必须这样做。（更多细节如下所示。）因此，复合常量的一般格式如下：
'( val1 , val2 , ... )'
'("fuzzy dice",42,1.99)'
这将是上面定义的 inventory_item 类型的有效值。要使字段为 NULL，请在其在列表中的位置不写任何字符。例如，此常量指定 NULL 第三个字段：
'("fuzzy dice",42,)'
ROW 表达式语法也可用于构造复合值。在大多数情况下，这比使用字符串文字语法要简单得多，因为您不必担心多层引用。上面我们已经使用了这个方法：
ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)
只要表达式中有多个字段，ROW 关键字实际上是可选的，因此可以简化为：
('fuzzy dice', 42, 1.99)
('', 42, NULL)
要访问复合列的字段，需要写入一个点和字段名称，就像从表名称中选择一个字段一样。事实上，这非常类似于从表名中进行选择，以至于您经常必须使用括号来避免混淆解析器。例如，您可能会尝试从我们的 on_hand 示例表中选择一些子字段，例如：
SELECT item.name FROM on_hand WHERE item.price > 9.99;
这将不起作用，因为根据 SQL 语法规则，名称 item 被视为表名，而不是 on_hand 的列名。你必须这样写：
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
或者如果您还需要使用表名（例如在多表查询中），如下所示：
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
现在括号中的对象被正确解释为对 item 列的引用，然后可以从中选择子字段。
以下是插入和更新复合列的正确语法的一些示例。首先，插入或更新一整列：
INSERT INTO mytab (complex_col) VALUES((1.1,2.2));
UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
我们可以更新复合列的单个子字段：
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
请注意，我们不需要（实际上也不能）在 SET 之后出现的列名周围加上括号，但是当在等号右侧引用表达式中的同一列时，我们确实需要括号。
我们也可以将子字段指定为 INSERT 的目标：
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
在 PostgreSQL 中，查询中对表名（或别名）的引用实际上是对表当前行的复合值的引用。例如，如果我们有一个如上所示的表on_hand，我们可以这样写：
SELECT c FROM on_hand c;
PostgreSQL 会将这种扩展行为应用到任何复合值表达式，尽管如上所示，当 .* 不是一个简单的表名时，您需要在应用的值周围写上括号。例如，如果 myfunc() 是返回包含 a、b 和 c 列的复合类型的函数，则这两个查询具有相同的结果：
SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;
当出现在 SELECT 输出列表、INSERT/UPDATE/DELETE 中的 RETURNING 列表、VALUES 子句或行构造函数的顶层时，composite_value.* 语法会导致这种列扩展。在所有其他上下文中（包括嵌套在其中一个构造中时），将 .* 附加到复合值不会更改该值，因为它表示“所有列”，因此再次生成相同的复合值。例如，如果 somefunc() 接受复合值参数，则这些查询是相同的：
SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;
在这两种情况下，inventory_item 的当前行作为单个复合值参数传递给函数。尽管 .* 在这种情况下什么都不做，但使用它是一种很好的风格，因为它清楚地表明了复合值的意图。特别是，解析器会认为 c.* 中的 c 是指表名或别名，而不是列名，这样就没有歧义；而没有 .*，则不清楚 c 是表名还是列名，实际上如果存在名为 c 的列，则列名解释将是首选。
另一个展示这些概念的例子是所有这些查询都意味着同样的事情：
SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);
所有这些 ORDER BY 子句都指定行的复合值。但是，如果inventory_item 包含名为c 的列，则第一种情况将与其他情况不同，因为这意味着仅按该列排序。鉴于前面显示的列名，这些查询也等同于上面的查询：
SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);
与复合值相关的另一个特殊语法行为是我们可以使用函数表示法来提取复合值的字段。解释这一点的简单方法是符号 field(table) 和 table.field 是可以互换的。例如，这些查询是等效的：
SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;
select c.*
from sal_emp c
where name(c) is not null;
此外，如果我们有一个接受复合类型的单个参数的函数，我们可以使用任何一种表示法来调用它。这些查询都是等价的：
SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;
函数表示法和字段表示法之间的这种等效性使得可以在复合类型上使用函数来实现“计算字段”。使用上面最后一个查询的应用程序不需要直接知道 somefunc 不是表的真实列。
复合值的外部文本表示由根据各个字段类型的 I/O 转换规则解释的项目以及指示复合结构的装饰组成。装饰由围绕整个值的括号 (( 和 )) 以及相邻项之间的逗号 (,) 组成。括号外的空格被忽略，但在括号内，它被视为字段值的一部分，根据字段数据类型的输入转换规则，它可能重要也可能不重要。例如，在：
'( 42)'
如果字段类型是整数，则空格将被忽略，但如果是文本则不会。
如前所述，在编写复合值时，您可以在任何单个字段值周围加上双引号。如果字段值会混淆复合值解析器，则必须这样做。特别是，包含括号、逗号、双引号或反斜杠的字段必须用双引号引起来。要将双引号或反斜杠放在带引号的复合字段值中，请在其前面加上反斜杠。（此外，双引号字段值中的一对双引号用于表示双引号字符，类似于 SQL 文字字符串中的单引号规则。）或者，您可以避免引用并使用反斜杠转义来保护否则将被视为复合语法的所有数据字符。
