外部数据封装与模糊匹配

本文件包含模块：file_fdw（文件外部数据封装）、fuzzystrmatch（模糊字符串匹配）、hstore（键值对存储）、intagg（整数聚合）、intarray（整数数组）。

file_fdw

模块提供外部数据包装器file_fdw，它能被用来访问服务器的文件系统中的数据文件，或者在服务器上执行程序并读取它们的输出。数据文件或程序输出必须是能够被COPY FROM读取的格式。

用这个包装器创建的一个外部表可以有下列选项：

filename 指定要被读取的文件。相对路径是相对于数据目录。必须指定filename或program，但不能同时指定两个。

program 指定要执行的命令。该命令的标准输出将被读取，就像使用COPY FROM PROGRAM一样。必须指定program 或filename，但不能同时指定两个。

format 指定数据的格式，和COPY的FORMAT选项相同。

header 指定数据是否具有一个头部行，和COPY的HEADER选项相同。

delimiter 指定数据的定界符字符，和COPY的DELIMITER选项相同。

quote 指定数据的引用字符，和COPY的QUOTE选项相同。

escape 指定数据的转义字符，和COPY的ESCAPE选项相同。

null 指定数据的空字符串，和COPY的NULL选项相同。

encoding 指定数据的编码，和COPY的ENCODING选项相同。

注意：虽然COPY允许诸如HEADER的选项不用一个相应的值指定，但是外部表选项语法要求在所有情况下都出现一个值。要激活通常写入没有值的 COPY选项，你可以传递值 TRUE，因为所有这些选项都是布尔值。使用这个包装器创建的表的一列可以具有下列选项：force_not_null 这是一个布尔选项。如果为真，它指定该列的值不应该与空字符串匹配（也就是表级别的null选项）。这和把该列放在COPY的FORCE_NOT_NULL选项中具有相同的效果。

force_null 这是一个布尔选项。如果为真，它指定匹配空值字符串的列值会被返回为NULL，即使该值被引号引用。如果没有这个选项，只有匹配空值字符串的未被引用的值会被返回为 NULL。这和在COPY的FORCE_NULL 选项中列出该列有同样的效果。

COPY的FORCE_QUOTE选项当前不被file_fdw支持。

这些选项只能为一个外部表及其列指定，而不能在file_fdw外部数据包装器的选项中指定，也不能在使用该包装器的服务器或者用户映射的选项中指定。

出于安全原因，改变表级别的选项要求超级用户特权或具有默认角色pg_read_server_files（使用文件名）或默认角色pg_execute_server_program （使用程序）的权限：只有特定用户能够控制读取哪个文件或者运行哪个程序。原则上普通用户可以被允许改变其它选项，但是当前还不支持这样做。

当指定program选项时，请记住，选项字符串是通过shell执行的。如果想传递任何参数到不受信任的源命令，必须小心去掉或转义任何对shell来说可能有特殊含义的字符。安全起见，最好使用固定的命令字符串，或者至少避免传递任何用户输入。

对于一个使用file_fdw的外部表，EXPLAIN显示要读取的文件名或要运行的程序。对于文件来说，除非指定COSTS OFF，否则文件尺寸（以字节计）也会被显示。

例 D.1. 为 CSV 日志创建一个外部表

一种file_fdw的用法是把可用的 Halo 活动日志变成一个表用于查询。要这样做，首先你必须写日志到一个CSV文件,这里我们将它称为pglog.csv。首先，将file_fdw安装为一个扩展：

test=# CREATE EXTENSION file_fdw;
CREATE EXTENSION

然后创建一个外部服务器：

test=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

现在你已经准备好创建外部数据表。使用CREATE FOREIGN TABLE命令，你将需要为该表定义列、CSV 文件名以及格式：

test=# CREATE FOREIGN TABLE pglog (
test(# log_time timestamp(3) with time zone,
test(# user_name text,
test(# database_name text,
test(# process_id integer,
test(# connection_from text,
test(# session_id text,
test(# session_line_num bigint,
test(# command_tag text,
test(# session_start_time timestamp with time zone, virtual_transaction_id text,
test(# transaction_id bigint,
test(# error_severity text,
test(# sql_state_code text,
test(# message text,
test(# detail text,
test(# hint text, internal_query text,
test(# internal_query_pos integer,
test(# context text,
test(# query text,
test(# query_pos integer,
test(# location text,
test(# application_name text,
test(# backend_type text
test(# ) SERVER pglog
test-# OPTIONS ( filename 'log/pglog.csv', format 'csv' );
CREATE FOREIGN TABLE

现在你能够直接查询你的日志了。当然，在生产中你会需要定义一些方法来处理日志轮转。

fuzzystrmatch

fuzzystrmatch模块提供多个函数来判断字符串之间的相似性和距离。

小心：当前，soundex、metaphone、dmetaphone和dmetaphone_alt函数使用多字节编码下工作得不好。

这个模块被视为"trusted"，就是说，它可由在当前数据库上拥有CREATE特权的非超级用户安装。

Soundex

语音表示法系统是一种将相似发音的名字转换成相同的代码来匹配它们的方法。这最初由美国国家统计局在1880年、1900年和1910年使用。注意语音表示法对于非英语名称不是很有用。

fuzzystrmatch模块提供了两个函数用于语音表示法代码：

soundex(text) 返回

text difference(text, text) 返回 int

soundex函数将一个字符串转换成它的语音表示法代码。

difference函数将两个字符串转换成它们的语音表示法代码，并且接着报告能匹配代码位置的数量。由于语音表示法代码具有四个字符，结果可以从零到四，零表示没有匹配而四表示完全匹配（因此这个函数的命名并不适当 --- similarity才是更合适的名称）。

这里有一些例子：

test=# CREATE EXTENSION fuzzystrmatch ;
CREATE EXTENSION
test=# SELECT soundex('hello world!');
soundex
---------
H464
(1 row)
test=# SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');
soundex | soundex | difference
---------+---------+------------
A500 | A500 | 4
(1 row)
test=# \x on
扩展显示已打开
test=# SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
-[ RECORD 1 ]----
soundex    | A500
soundex    | A536
difference | 2
test=# SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');
-[ RECORD 1 ]----
soundex    | A500
soundex    | M626
difference | 0
test=# CREATE TABLE s (nm text);
CREATE TABLE
test=# INSERT INTO s VALUES ('john');
INSERT 0 1
test=# INSERT INTO s VALUES ('joan');
INSERT 0 1
test=# INSERT INTO s VALUES ('wobbly');
INSERT 0 1
test=# INSERT INTO s VALUES ('jack');
INSERT 0 1
test=# SELECT * FROM s WHERE soundex(nm) = soundex('john');
nm
------
john
joan
(2 rows)
test=# SELECT * FROM s WHERE difference(s.nm, 'john') > 2;
nm
------
john
joan
jack
(3 rows)

Levenshtein

这个函数计算两个字符串之间的编辑距离。

levenshtein(text source, text target, int ins_cost, int del_cost, int sub_cost) 返回 int

levenshtein(text source, text target) 返回 int

levenshtein_less_equal(text source, text target, int ins_cost, int del_cost, int sub_cost, int max_d) 返回 int

levenshtein_less_equal(text source, text target, int max_d) 返回 int

source以及target都可以是任何非空字符串，最长为 255 个字符。代价参数分别指定一个字符插入、删除或替换的开销。你可以像这个函数的第二种版本那样忽略代价参数，那样它们都会默认为 1。

levenshtein_less_equal是 Levenshtein 函数的速度更快的版本，它被用于只对小距离感兴趣的情况。如果实际距离小于等于max_d，那么levenshtein_less_equal返回正确的距离。否则它返回某个大于max_d的值。如果max_d是负值，那么其行为等同于 levenshtein。

例子：

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
levenshtein
-------------
2
(1 row)
test=# SELECT levenshtein('GUMBO', 'GAMBOL', 2,1,1);
levenshtein
-------------
3
(1 row)
test=# SELECT levenshtein_less_equal('extensive','exhaustive',2);
levenshtein_less_equal
------------------------
3
(1 row)
test=# SELECT levenshtein_less_equal('extensive','exhaustive',4);
levenshtein_less_equal
------------------------
4
(1 row)

Metaphone

和 Soundex 相似，Metaphone 的思想是构建一个输入字符串的一种代码。如果两个字符串具有相同的代码则认为它们相似。

这个函数计算一个输入字符串的变音位代码：

metaphone(text source, int max_output_length) 返回 text

source必须是一个非空字符串，最大长度为255个字符。max_output_length设置输出的变音位代码的最大长度，如果超长，输出会被截断到这个长度。

例子：

test=# SELECT metaphone('GUMBO', 4);
metaphone
-----------
KM
(1 row)

双 Metaphone

双变音位系统为一个给定输入字符串计算两个"听起来像的"字符串 --- 一个"主要"代码和一个"次要"代码。在大部分情况下它们是相同的，但是对于非英语名称它们可能有一点不同，这取决于发音。这些函数计算主要和次要代码：

dmetaphone(text source) 返回 text

dmetaphone_alt(text source) 返回 text

对输入字符串没有长度限制。

例子：

test=# SELECT dmetaphone('gumbo');
dmetaphone
------------
KMP
(1 row)

hstore

这个模块实现了hstore数据类型用来在一个单一值中存储键值对。这在很多情景下都有用，例如带有很多很少被检查的属性的行或者半结构化数据。键和值都是简单的文本字符串。

这个模块被视为"trusted"，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。

hstore 外部表示

一个hstore的文本表示用于输入和输出，包括零个或者多个由逗号分隔的key => value对。一些例子：

k => v
foo => bar, baz => whatever
"1-a" => "anything at all"

键值对的顺序没有意义（并且在输出时也不会重现）。键值对之间或者=>号周围的空白会被忽略。双引号内的键和值可以包括空白、逗号、=或>。要在一个键或值中包括一个双引号或一个反斜线，用一个反斜线对它转义。

一个hstore中的每一个键是唯一的。如果你声明了一个有重复键的hstore，只有一个会被存储在hstore中并且无法保证哪一个将被保留：

test=# create extension hstore;
test=# SELECT 'a=>1,a=>2'::hstore;
hstore
----------
"a"=>"1"
(1 row)

一个值（但不是一个键）能够是一个 SQL NULL。例如：

key => NULL

NULL关键词是大小写不敏感的。将NULL放在双引号中可以将它当作一个普通的字符串"NULL"。

注意：记住当hstore文本格式当被用于输入时，它应用在任何必须的引用或转义之前。如果你通过一个参数传递一个hstore文字，那么不需要额外的处理。但是如果你将它作为一个引用的文字常数，那么任何单引号字符以及（取决于standard_conforming_strings配置参数的设置）反斜线字符需要被正确地转义。

在输出时，双引号总是围绕着键和值，即使这样做不是绝对必要。

hstore 操作符和函数

hstore模块所提供的操作符显示在表 D.7中，函数在表 D.8中。

表 D.7. hstore 操作符

操作符/描述/例子：

hstore -> text → text：返回与给定键相关联的值，如果不存在则返回NULL。'a=>x, b=>y'::hstore -> 'a' → x
hstore -> text[] → text[]：返回与给定键(多个)相关联的值，如果不存在则返回NULL。 'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] → {"z","x"} hstore \
hstore ? text → boolean：hstore 是否包含键? 'a=>1'::hstore ? 'a' → t
hstore ?& text[] → boolean：hstore 是否包含所有指定的键? 'a=>1,b=>2'::hstore ?& ARRAY['a','b'] → t hstore ?\
hstore @> hstore → boolean：左操作符包含右操作符吗? 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' →t
hstore - text → hstore：从左操作符删除键。 'a=>1, b=>2, c=>3'::hstore - 'b'::text → "a"=>"1", "c"=>"3"
hstore - text[] → hstore：从左操作符删除键(多个)。 'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] → "c"=>"3"
hstore - hstore → hstore：从左操作符中删除与右操作符中的对(pairs)相匹配的对。 'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore → "a"=>"1", "c"=>"3"
anyelement #= hstore → anyelement：用hstore中相匹配值替换左操作符(必须是复合类型)中的字段。 ROW(1,3) #= 'f1=>11'::hstore → (11,3) %% hstore → text[] 将hstore转换为交替键和值的数组。 %% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar} %# hstore → text[] 将hstore转换为二维的键/值数组。%# 'a=>foo, b=>bar'::hstore → {{a,foo},{b,bar}}

注意：操作符@>和<@分别被称为@和~。这些名称仍然可用，但是已经被弃用并且最终将被移除。旧名称和原来核心几何数据类型所遵循的习惯是相反的！

表 D.8. hstore 函数

函数/描述：/例子：

hstore ( record ) → hstore：从一个记录或行构造一个hstore。 hstore(ROW(1,2)) → "f1"=>"1", "f2"=>"2"
hstore ( text[] ) → hstore：从一个数组构造hstore，可以是键/值数组，也可以是二维数组。hstore(ARRAY['a','1','b','2']) → "a"=>"1", "b"=>"2" hstore(ARRAY[['c','3'],['d','4']]) → "c"=>"3", "d"=>"4"
hstore ( text[], text[] ) → hstore：从单独的键和值数组构造一个hstore。 hstore(ARRAY['a','b'], ARRAY['1','2']) → "a"=>"1", "b"=>"2"
hstore ( text, text ) → hstore：创建一个单项目 hstore。 hstore('a', 'b') → "a"=>"b"
akeys ( hstore ) → text[]：提取一个hstore的键作为数组。 akeys('a=>1,b=>2') → {a,b}
skeys ( hstore ) → setof text：提取一个hstore的键作为一个集合。
skeys('a=>1,b=>2') → a：b
avals ( hstore ) → text[]：提取一个hstore的值为一个数组。 avals('a=>1,b=>2') → {1,2}
svals ( hstore ) → setof text：提取一个hstore的值为一个集合。 svals('a=>1,b=>2') → 1 2
hstore_to_array ( hstore ) → text[]：提取一个hstore的键和值为键和值交替数组。 hstore_to_array('a=>1,b=>2') → {a,1,b,2}
hstore_to_matrix ( hstore ) → text[]：提取hstore的键和值为二维数组。hstore_to_matrix('a=>1,b=>2') → {{a,1},{b,2}}
hstore_to_json ( hstore ) → json：将hstore转换为json值，将所有非空值转换为JSON字符串。当hstore值被转换为json时，隐式使用此函数。 hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_jsonb ( hstore ) → jsonb：将hstore转换为jsonb值，将所有非空值转换为JSON字符串。当hstore值转换为jsonb时，隐式使用此函数。 hstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_json_loose ( hstore ) → json：将hstore转换为json值，但尝试区分数值和布尔值，所有它们在JSON中未引用。 hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
hstore_to_jsonb_loose ( hstore ) → jsonb：将hstore转换为jsonb值，但试图区分数值和布尔值，使它们在JSON中不带引号。 hstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
slice ( hstore, text[] ) → hstore：提取仅包含指定键的hstore的子集。 slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']) → "b"=>"2", "c"=>"3"
each ( hstore ) → setof record：( key text, value text ) 提取hstore的键和值作为一组记录。 select * from
each('a=>1,b=>2') → key：\ -----+------- a \ b \
exist ( hstore, text ) → boolean：hstore 是否包含键?
exist('a=>1', 'a') → t
defined ( hstore, text ) → boolean：hstore 是否包含针对键的非NULL 值？
defined('a=>NULL', 'a') → f
delete ( hstore, text ) → hstore：删除带有匹配键的对(pair)。 delete('a=>1,b=>2', 'b') → "a"=>"1"
delete ( hstore, text[] ) → hstore：删除带有匹配键的对(pairs)。 delete('a=>1,b=>2,c=>3', ARRAY['a','b']) → "c"=>"3
delete ( hstore, hstore ) → hstore：删除在第二个参数中匹配的对。 delete('a=>1,b=>2', 'a=>4,b=>2'::hstore) → "a"=>"1"
populate_record ( anyelement, hstore ) → anyelement：用hstore中的匹配值替换左操作符(必须是复合类型)中的字段。 populate_record(ROW(1,2), 'f1=>42'::hstore) → (42,2)

索引

hstore有对@>、?、?&和?|操作符的 GiST 和 GIN 索引支持。例如：

CREATE INDEX hidx ON testhstore USING GIST (h);
CREATE INDEX hidx ON testhstore USING GIN (h);

gist_hstore_ops GiST 操作符类(opclass)将一组键/值对近似计算为位图签名。它的可选整数参数siglen决定了签名的字节长度。默认长度为16字节。签名长度的有效值在1到2024字节之间。更长的签名将导致更精确的搜索(扫描更小的索引部分和更少的堆页)，以更大的索引为代价。

建立这样的一个带有32字节签名长度的示例：

CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));

hstore也为=操作符支持btree或hash索引。这允许hstore列被声明为UNIQUE或者被使用在GROUP BY、ORDER BY或DISTINCT表达式中。hstore值的排序顺序不是特别有用，但是这些索引可能对等值查找有用。为等比较创建以下索引：

CREATE INDEX hidx ON testhstore USING BTREE (h);
CREATE INDEX hidx ON testhstore USING HASH (h);

例子

增加一个键，或者用一个新值更新一个现有的键：

UPDATE tab SET h = h || hstore('c', '3');

删除一个键：

UPDATE tab SET h = delete(h, 'k1');

将一个record转换成一个hstore：

test=# CREATE TABLE test (col1 integer, col2 text, col3 text);
CREATE TABLE
test=# INSERT INTO test VALUES (123, 'foo', 'bar');
INSERT 0 1
test=# SELECT hstore(t) FROM test AS t;
hstore
---------------------------------------------
"col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)

将一个hstore转换成一个预定义的record类型：

test=# CREATE TABLE test (col1 integer, col2 text, col3 text);
CREATE TABLE
test=# SELECT * FROM populate_record(null::test,
test(# '"col1"=>"456", "col2"=>"zzz"');
col1 | col2 | col3
------+------+------
456 | zzz |
(1 row)

用来自于一个hstore的值修改一个现有的记录：

test=# CREATE TABLE test (col1 integer, col2 text, col3 text);
CREATE TABLE
test=# INSERT INTO test VALUES (123, 'foo', 'bar');
INSERT 0 1
test=# SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
col1 | col2 | col3
------+------+------
123 | foo | baz
(1 row)

统计

由于hstore类型本质的宽大性，它能够包含一些不同的键。检查合法键是应用的任务。下列例子验证了用于检查键以及获得统计的一些技术。

简单例子：

test=# SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
key | value
-----+-------
| 1
b |
aaa | bq
(3 rows)

使用一个表：

SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;

在线统计：

SELECT key, count(*) FROM (SELECT (each(h)).key FROM testhstore) AS stat GROUP BY key ORDER BY count DESC, key;
key | count
-----------+-------
line | 883
query | 207
pos | 203
node | 202
space | 197
status | 195
public | 194
title | 190
org | 189 ...................

兼容性

hstore使用不会为转储/恢复升级造成障碍，因为文本表示（用于转储）没有改变。

在一次二进制升级中，通过让新代码识别旧格式数据来维持向上兼容。当处理还没有被新代码修改过的数据时，这会带来一定的性能惩罚。可以通过执行一个下面的UPDATE语句来强制升级表中的所有值：

UPDATE tablename SET hstorecol = hstorecol || '';

另一种方法：

ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || ''; ALTER TABLE方法要求表上的一个排他锁，但是不会导致表因为旧行版本而膨胀。

转换

有一些额外的扩展为语言 PL/Perl 和 PL/Python 实现了hstore 类型的转换。用于 PL/ Perl 的扩展叫做hstore_plperl和 hstore_plperlu，分别用于可信的和不可信的 PL/Perl。如果安装这些转换并且在创建函数时指定它们，hstore值会被映射成 Perl 。用于 PL/Python 的扩展是hstore_plpythonu、 hstore_plpython2u和hstore_plpython3u。如果使用它们，hstore值会被映射成 Python 字典。

小心：强烈建议将转换扩展安装在与hstore相同的模式中。否则，如果转换扩展的模式包含敌对用户定义的对象，就会存在安装时的安全隐患。

intagg

intagg模块提供了一个整数聚集器以及一个枚举器。intagg现在已被弃用，因为有内建的函数能提供其功能的超集。不过，该模块仍然作为内建函数的一个兼容性包装器被提供。

函数

聚集器是一个聚集函数int_array_aggregate(integer)，它产生一个完全包含输入整数的整数数组。这是一个array_agg的包装器，它对任何数组类型做同样的事情。

枚举器是一个函数int_array_enum(integer[])，它返回setof integer。它本质上是聚集器的一个逆操作：给定一个整数数组，将它展开成行的集合。这是unnest的一个包装器，它对任何数组类型做相同的事情。

使用示例

很多数据库系统都有一对多表的概念这样一个表通常位于两个被索引表之间，例如：

CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

通常这样用它：

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) WHERE one_to_many.left = item;

这将为左手表中的一个项返回右手表中的所有项。现在，这种方法对于具有非常多项的one_to_many表会有麻烦。通常，这样的一次连接将为一个特定的左手项导致一次索引扫描以及为每一个右手项导致一次取出。如果你有一个非常动态的系统，你没什么可做的。但是，如果你的据相对比较静态，你可以使用聚集器创建一个汇总表。

CREATE TABLE summary AS
SELECT left, int_array_aggregate(right) AS right
FROM one_to_many GROUP BY left;

这将创建一个表，其中对每一个左项都有一行，并且有一个右项的数组。现在如果没有某种方法来使用该数组，这样做是没有任何用处的。这时数组枚举器就派上用场了，你可以

SELECT left, int_array_enum(right) FROM summary WHERE left = item; 上述使用int_array_enum的查询产生与以下语句相同的结果

SELECT left, right FROM one_to_many WHERE left = item;

区别是针对汇总表的查询必须只从表中得到一行，而针对one_to_many的直接查询必须索引扫描并且为每项都获取一行。

在一个系统上，一个EXPLAIN显示一个查询的代价从 8488 降低到了 329。原始查询是一个涉及到one_to_many表的连接，它被替换为：

SELECT right, count(right) FROM ( SELECT left, int_array_enum(right) AS right FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts ON (summary.left = lefts.left)
) AS list
GROUP BY right ORDER BY count DESC;

intarray

intarray模块提供了一些有用的函数和操作符来操纵不含空值的整数数组。也提供了对使用某些操作符的索引搜索的支持。

如果一个提供的数组中包含任何 NULL 元素，所有这些操作都将抛出一个错误。很多这些操作只对一维数组有意义。尽管它们将接受更多维数的数组输入，数据将被当作一个按照存储顺序排列的线性数组对待。

该模块被认为是"trusted"，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。

intarray 函数和操作符

intarray模块提供的函数被列在表 D.9中，操作符被列在表 D.10中。

表 D.9. intarray 函数

函数/描述：/例子：

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[]：移除临近的重复 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}

表 D.10. intarray 操作符

操作符/描述

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 数组是否满足查询

操作符&&、@>和<@等效于PostgreSQL的内建同名操作符，不过它们只能在不含空值的整数数组上工作，而内建的操作符可以对任何数组类型工作。这种限制使它们在很多情况下比内建操作符更快。

@@和~~操作符测试一个数组是否满足一个query，它被表示成一种特殊数据类型query_int的一个值。一个由整数值组成的查询会被针对数组的元素检查，可能会组合使用操作符&（AND）、|（OR）以及!（NOT）。根据需要可以使用圆括号。例如，查询1&(2|3)匹配包含 1 并且还包括 2 或 3 的数组。

索引支持

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 的相对性能特点，这将在其他地方讨论。

例子

-- 一个消息可以在一个或多个"小节"中 CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);
-- 创建签名长度为32 字节的专用索引CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__int_ops(siglen=32)); -- 选择小节1或2中的消息 - OVERLAP 操作符 SELECT message.mid FROM message WHERE message.sections && '{1,2}';
-- 选择小节1和2 中的消息 - CONTAINS 操作符 SELECT message.mid FROM message WHERE message.sections @> '{1,2}';
-- 相同，使用 QUERY 操作符 SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;