Postgresql 中文操作指南

F.20. intarray — manipulate arrays of integers #

intarray 模块提供了一些实用函数和运算符，用于操作无空整数数组。还支持使用某些运算符进行索引搜索。

如果提供的数组包含任何 NULL 元素，所有这些操作都将引发错误。

其中的许多操作仅对一维数组有意义。尽管它们会接受更多维的输入数组，但数据在处理中将视为存储顺序中的线性数组。

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。

F.20.1. intarray Functions and Operators #

intarray 模块提供的函数如 Table F.9 所示，操作符如 Table F.10 所示。

Table F.9. intarray Functions

Function

Description

Example(s)

icount ( integer[] ) → integer 返回数组中的元素数量。 icount('{1,2,3}'::integer[]) → 3

sort ( integer[] , dir text ) → integer[] 按升序或降序对数组进行排序。 dir 必须为 asc 或 desc 。 sort('{1,3,2}'::integer[], 'desc') → {3,2,1}

sort ( integer[] ) → integer[] sort_asc ( integer[] ) → integer[] 按升序排序。 sort(array[11,77,44]) → {11,44,77}

sort_desc ( integer[] ) → integer[] 按降序排序。 sort_desc(array[11,77,44]) → {77,44,11}

uniq ( integer[] ) → integer[] 删除相邻的重复项。通常与 sort 一起使用，以删除所有重复项。 uniq('{1,2,2,3,1,1}'::integer[]) → {1,2,3,1} uniq(sort('{1,2,3,2,1}'::integer[])) → {1,2,3}

idx ( integer[] , item integer ) → integer 返回第一个与 item 匹配的数组元素的索引，如果没有匹配项则返回 0。 idx(array[11,22,33,22,11], 22) → 2

subarray ( integer[] , start integer , len integer ) → integer[] 提取从位置 start 开始，包含 len 个元素的部分数组。 subarray('{1,2,3,2,1}'::integer[], 2, 3) → {2,3,2}

subarray ( integer[] , start integer ) → integer[] 提取从位置 start 开始的部分数组。 subarray('{1,2,3,2,1}'::integer[], 2) → {2,3,2,1}

intset ( integer ) → integer[] 生成单元素数组。 intset(42) → {42}

Table F.10. intarray Operators

Operator

Description

integer[] && integer[] → boolean 数组是否有重叠（至少有一个公共元素）？

integer[] @> integer[] → boolean 左数组是否包含右数组？

integer[] <@ integer[] → boolean 右数组是否包含左数组？

# integer[] → integer 返回数组中的元素数量。

integer[] # integer → integer 返回第一个与右参数匹配的数组元素的索引，如果没有匹配项则返回 0。（与 idx 函数相同。）

integer[] + integer → integer[] 向数组末尾添加元素。

integer[] + integer[] → integer[] 连接数组。

integer[] - integer → integer[] 从数组中移除与右参数匹配的条目。

integer[] - integer[] → integer[] 从左数组中移除右数组的元素。

integer[] _

_ integer → integer[] 计算参数的并集。

integer[] _

_ integer[] → integer[] 计算参数的并集。

integer[] & integer[] → integer[] 计算参数的交集。

integer[] @@ query_int → boolean 数组是否满足查询？（见下文）

query_int ~~ integer[] → boolean 数组是否满足查询？（ @@ 的换向器）

运算符 &&、@> 和 <@ 等同于 PostgreSQL 的同名内置运算符，但它们仅在不包含空值的整数数组中起作用，而内置运算符适用于任何数组类型。这种限制使得它们在很多情况下比内置运算符更快。

@@ 和 ~~ 运算符测试数组是否满足 query，后者表示为专门数据类型 query_int 的值。一个 query 包含检查对数组元素的整型值，可能通过以下运算符组合实现：& (AND)、| (OR) 和 ! (NOT)。必要时可以使用括号。例如，查询 1&(2|3) 匹配既包含 1 又包含 2 或 3 的数组。

F.20.2. Index Support #

intarray 为运算符 &&、@> 和 @@ 提供了索引支持，以及常规数组相等性。

提供了两个参数化 GiST 索引运算符类：gist_int_ops（默认使用）适合于中小型数据集，而 gist_intbig_ops 则使用更大的签名，更适合于索引大型数据集（即包含大量不同数组值列）。该实现使用内置的有损压缩功能的 RD 树数据结构。

gist__int_ops 通过一组整数范围逼近一个整数集。它的可选整数参数 numranges 确定一个索引键中范围的最大数量。numranges 的默认值为 100。有效值介于 1 和 253 之间。使用更大的数组作为 GiST 索引键会导致更精确的搜索（扫描较小部分的索引和较少的堆页），代价是索引更大。

gist__intbig_ops 通过位图签名逼近一个整数集。它的可选整数参数 siglen 以字节为单位确定签名长度。默认签名长度为 16 个字节。有效的签名长度介于 1 和 2024 个字节之间。更长的签名会导致更精确的搜索（扫描更小部分的索引和更少的堆页），代价是索引更大。

还有一类非默认 GIN 运算符类 gin__int_ops，它支持这些运算符以及 <@。

在 GiST 和 GIN 索引之间的选择取决于 GiST 和 GIN 的相对性能特性，这一点在其他地方已经讨论过了。

F.20.3. Example #

-- a message can be in one or more “sections”
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);

-- create specialized index with signature length of 32 bytes
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));

-- select messages in section 1 OR 2 - OVERLAP operator
SELECT message.mid FROM message WHERE message.sections && '{1,2}';

-- select messages in sections 1 AND 2 - CONTAINS operator
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

-- the same, using QUERY operator
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

F.20.4. Benchmark #

源目录 contrib/intarray/bench 包含一个基准测试套件，可以在已安装 PostgreSQL 服务器上运行。（它还需要安装 DBD::Pg。）要运行：

cd .../contrib/intarray/bench
createdb TEST
psql -c "CREATE EXTENSION intarray" TEST
./create_test.pl | psql TEST
./bench.pl

bench.pl 脚本有许多选项，在没有参数的情况下运行时将显示这些选项。

F.20.5. Authors #

所有工作均由 Teodor Sigaev ( < link:mailto:teodor@sigaev.ru[teodor@sigaev.ru]> ) 和 Oleg Bartunov ( < link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]> ) 完成。有关其他信息，请参见：http://www.sai.msu.su/ _{megera/postgres/gist/[http://www.sai.msu.su/} megera/postgres/gist/。Andrey Oktyabrski 在添加新函数和操作方面做了出色的工作。