2024年11月21日: PostgreSQL 17.2、16.6、15.10、14.15、13.18 和 12.22 发布！
支持的版本: 当前 (17) / 16 / 15 / 14 / 13
开发版本: devel
不支持的版本: 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3
F.22. ltree — 分层树状数据类型
上一个  向上 附录 F. 其他提供的模块和扩展 首页  下一个

F.22. ltree — 分层树状数据类型 #

F.22.1. 定义
F.22.2. 操作符和函数
F.22.3. 索引
F.22.4. 示例
F.22.5. 转换
F.22.6. 作者

此模块实现了一种数据类型 ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。

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

F.22.1. 定义 #

标签是由字母数字字符、下划线和连字符组成的序列。有效的字母数字字符范围取决于数据库的区域设置。例如，在 C 区域设置中，允许的字符为 A-Za-z0-9_-。标签的长度不得超过 1000 个字符。

示例：42，Personal_Services

标签路径是零个或多个以点分隔的标签序列，例如 L1.L2.L3，表示从分层树的根到特定节点的路径。标签路径的长度不能超过 65535 个标签。

示例：Top.Countries.Europe.Russia

ltree 模块提供了几种数据类型

  • ltree 存储标签路径。

  • lquery 表示用于匹配 ltree 值的类似正则表达式的模式。一个简单的单词匹配路径中的该标签。星号符号 (*) 匹配零个或多个标签。这些可以用点连接起来，形成一个必须匹配整个标签路径的模式。例如

    foo         Match the exact label path foo
*.foo.*     Match any label path containing the label foo
*.foo       Match any label path whose last label is foo

    星号符号和简单单词都可以量化，以限制它们可以匹配的标签数量

    *{n}        Match exactly n labels
*{n,}       Match at least n labels
*{n,m}      Match at least n but not more than m labels
*{,m}       Match at most m labels — same as *{0,m}
foo{n,m}    Match at least n but not more than m occurrences of foo
foo{,}      Match any number of occurrences of foo, including zero

    在没有任何显式量词的情况下，星号符号的默认值是匹配任意数量的标签（即 {,}），而非星号项的默认值是完全匹配一次（即 {1}）。

    可以在非星号 lquery 项的末尾添加几个修饰符，使其匹配的不仅仅是精确匹配

    @           Match case-insensitively, for example a@ matches A
*           Match any label with this prefix, for example foo* matches foobar
%           Match initial underscore-separated words

    % 的行为有点复杂。它尝试匹配单词而不是整个标签。例如，foo_bar% 匹配 foo_bar_baz，但不匹配 foo_barbaz。如果与 * 组合，前缀匹配将分别应用于每个单词，例如 foo_bar%* 匹配 foo1_bar2_baz，但不匹配 foo1_br2_baz。

    此外，你可以编写几个可能被修改过的非星号项，用 | (OR) 分隔，以匹配这些项中的任何一个，并且你可以在非星号组的开头放置 ! (NOT)，以匹配不匹配任何替代项的任何标签。量词（如果有）位于组的末尾；它表示整个组的一些匹配次数（即，一些标签匹配或不匹配任何替代项）。

    以下是一个带有注释的 lquery 示例

    Top.*{0,2}.sport*@.!football|tennis{1,}.Russ*|Spain
a.  b.     c.      d.                   e.

    此查询将匹配任何标签路径，该标签路径

    1. 以标签 Top 开头

    2. 接下来有零到两个标签，然后是

    3. 以不区分大小写的前缀 sport 开头的标签

    4. 然后有一个或多个标签，其中任何一个都不匹配 football 或 tennis

    5. 然后以 Russ 开头或完全匹配 Spain 的标签结尾。

  • ltxtquery 表示用于匹配 ltree 值的类似全文搜索的模式。ltxtquery 值包含单词，末尾可能带有修饰符 @、*、%；修饰符的含义与 lquery 中的相同。可以使用 & (AND)、| (OR)、! (NOT) 和括号组合单词。与 lquery 的主要区别在于，ltxtquery 匹配单词，而无需考虑它们在标签路径中的位置。

    这是一个 ltxtquery 的示例

    Europe & Russia*@ & !Transportation

    这将匹配包含标签 Europe 和以 Russia 开头的任何标签（不区分大小写）的路径，但不匹配包含标签 Transportation 的路径。这些单词在路径中的位置并不重要。此外，当使用 % 时，可以将单词与标签中任何以 underscore 分隔的单词匹配，而无需考虑位置。

注意：ltxtquery 允许符号之间存在空格，但 ltree 和 lquery 不允许。

F.22.2. 操作符和函数 #

类型 ltree 具有常用的比较运算符 =, <>, <, >, <=, >=。比较按照树遍历的顺序进行排序，节点的子节点按标签文本排序。此外，表 F.12 中显示了专用运算符。

表 F.12. ltree 操作符

操作符

描述

ltree @> ltree → boolean

左侧参数是右侧参数的祖先（或相等）吗？

ltree <@ ltree → boolean

左侧参数是右侧参数的后代（或相等）吗？

ltree ~ lquery → boolean

lquery ~ ltree → boolean

ltree 是否匹配 lquery？

ltree ? lquery[] → boolean

lquery[] ? ltree → boolean

ltree 是否匹配数组中的任何 lquery？

ltree @ ltxtquery → boolean

ltxtquery @ ltree → boolean

ltree 是否匹配 ltxtquery？

ltree || ltree → ltree

连接 ltree 路径。

ltree || text → ltree

text || ltree → ltree

将文本转换为 ltree 并连接。

ltree[] @> ltree → boolean

ltree <@ ltree[] → boolean

数组是否包含 ltree 的祖先？

ltree[] <@ ltree → boolean

ltree @> ltree[] → boolean

数组是否包含 ltree 的后代？

ltree[] ~ lquery → boolean

lquery ~ ltree[] → boolean

数组是否包含任何与 lquery 匹配的路径？

ltree[] ? lquery[] → boolean

lquery[] ? ltree[] → boolean

ltree 数组是否包含任何与任何 lquery 匹配的路径？

ltree[] @ ltxtquery → boolean

ltxtquery @ ltree[] → boolean

数组是否包含任何与 ltxtquery 匹配的路径？

ltree[] ?@> ltree → ltree

返回数组中第一个是 ltree 祖先的条目，如果没有则返回 NULL。

ltree[] ?<@ ltree → ltree

返回数组中第一个是 ltree 后代的条目，如果没有则返回 NULL。

ltree[] ?~ lquery → ltree

返回数组中第一个与 lquery 匹配的条目，如果没有则返回 NULL。

ltree[] ?@ ltxtquery → ltree

返回数组中第一个与 ltxtquery 匹配的条目，如果没有则返回 NULL。

运算符 <@、@>、@ 和 ~ 有相应的 ^<@、^@>、^@ 和 ^~，它们的区别在于不使用索引。这些仅用于测试目的。

可用函数在 表 F.13 中显示。

表 F.13. ltree 函数

函数

描述

示例

subltree ( ltree, start integer, end integer ) → ltree

返回从位置 start 到位置 end-1 (从 0 开始计数) 的 ltree 子路径。

subltree('Top.Child1.Child2', 1, 2) → Child1

subpath ( ltree, offset integer, len integer ) → ltree

返回从位置 offset 开始，长度为 len 的 ltree 子路径。如果 offset 为负数，则子路径从路径末尾的该位置开始。如果 len 为负数，则从路径末尾删除该数量的标签。

subpath('Top.Child1.Child2', 0, 2) → Top.Child1

subpath ( ltree, offset integer ) → ltree

返回从位置 offset 开始，延伸到路径末尾的 ltree 子路径。如果 offset 为负数，则子路径从路径末尾的该位置开始。

subpath('Top.Child1.Child2', 1) → Child1.Child2

nlevel ( ltree ) → integer

返回路径中标签的数量。

nlevel('Top.Child1.Child2') → 3

index ( a ltree, b ltree ) → integer

返回 b 在 a 中第一次出现的位置，如果没有找到则返回 -1。

index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6') → 6

index ( a ltree, b ltree, offset integer ) → integer

返回 b 在 a 中第一次出现的位置，如果没有找到则返回 -1。搜索从位置 offset 开始；负的 offset 表示从路径末尾的 -offset 标签开始。

index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6', -4) → 9

text2ltree ( text ) → ltree

将 text 转换为 ltree。

ltree2text ( ltree ) → text

将 ltree 转换为 text。

lca ( ltree [, ltree [, ... ]] ) → ltree

计算路径的最长公共祖先 (最多支持 8 个参数)。

lca('1.2.3', '1.2.3.4.5.6') → 1.2

lca ( ltree[] ) → ltree

计算数组中路径的最长公共祖先。

lca(array['1.2.3'::ltree,'1.2.3.4']) → 1.2

F.22.3. 索引 #

ltree 支持几种索引类型，可以加速指示的运算符

  • 基于 ltree 的 B 树索引：<、<=、=、>=、>

  • 基于 ltree 的哈希索引：=

  • 基于 ltree 的 GiST 索引 ( gist_ltree_ops 操作符类): <、<=、=、>=、>、@>、<@、@、~、?

    gist_ltree_ops GiST 操作符类将一组路径标签近似为位图签名。其可选的整数参数 siglen 确定签名的长度（以字节为单位）。默认签名长度为 8 个字节。长度必须是正数，且为 int 对齐（在大多数机器上为 4 个字节）的倍数，最大为 2024。较长的签名会导致更精确的搜索（扫描索引的更小部分和更少的堆页面），但代价是索引更大。

    以下是使用默认签名长度 8 个字节创建此类索引的示例

    CREATE INDEX path_gist_idx ON test USING GIST (path);

    以下是创建签名长度为 100 个字节的此类索引的示例

    CREATE INDEX path_gist_idx ON test USING GIST (path gist_ltree_ops(siglen=100));

  • 基于 ltree[] 的 GiST 索引 ( gist__ltree_ops 操作符类): ltree[] <@ ltree、ltree @> ltree[]、@、~、?

    gist__ltree_ops GiST 操作符类的工作方式类似于 gist_ltree_ops，并且还采用签名长度作为参数。gist__ltree_ops 中的 siglen 的默认值为 28 字节。

    以下是使用默认签名长度 28 个字节创建此类索引的示例

    CREATE INDEX path_gist_idx ON test USING GIST (array_path);

    以下是创建签名长度为 100 个字节的此类索引的示例

    CREATE INDEX path_gist_idx ON test USING GIST (array_path gist__ltree_ops(siglen=100));

    注意：此索引类型是有损的。

F.22.4. 示例 #

此示例使用以下数据（也可在源发行版中的文件 contrib/ltree/ltreetest.sql 中找到）

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);
CREATE INDEX path_hash_idx ON test USING HASH (path);

现在，我们有一个表 test，其中填充了描述如下层次结构的数据

                        Top
                     /   |  \
             Science Hobbies Collections
                 /       |              \
        Astronomy   Amateurs_Astronomy Pictures
           /  \                            |
Astrophysics  Cosmology                Astronomy
                                        /  |    \
                                 Galaxies Stars Astronauts

我们可以进行继承

ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science';
                path
------------------------------------
 Top.Science
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(4 rows)

以下是一些路径匹配的示例

ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*';
                     path
-----------------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Collections.Pictures.Astronomy
 Top.Collections.Pictures.Astronomy.Stars
 Top.Collections.Pictures.Astronomy.Galaxies
 Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)

ltreetest=> SELECT path FROM test WHERE path ~ '*[email protected].*';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

以下是一些全文搜索的示例

ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Hobbies.Amateurs_Astronomy
(4 rows)

ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

使用函数构造路径

ltreetest=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
                 ?column?
------------------------------------------
 Top.Science.Space.Astronomy
 Top.Science.Space.Astronomy.Astrophysics
 Top.Science.Space.Astronomy.Cosmology
(3 rows)

我们可以通过创建一个 SQL 函数来简化此操作，该函数在路径中的指定位置插入标签

CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree
    AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'
    LANGUAGE SQL IMMUTABLE;

ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy';
                ins_label
------------------------------------------
 Top.Science.Space.Astronomy
 Top.Science.Space.Astronomy.Astrophysics
 Top.Science.Space.Astronomy.Cosmology
(3 rows)

F.22.5. 转换 #

ltree_plpython3u 扩展为 PL/Python 中的 ltree 类型实现了转换。如果在创建函数时安装并指定，则 ltree 值将映射到 Python 列表。（但是，目前不支持反向转换。）

注意

强烈建议将转换扩展安装在与 ltree 相同的模式中。否则，如果转换扩展的模式包含由恶意用户定义的对象，则在安装时会存在安全风险。

F.22.6. 作者 #

所有工作均由 Teodor Sigaev () 和 Oleg Bartunov () 完成。有关更多信息，请参见 http://www.sai.msu.su/~megera/postgres/gist/。作者感谢 Eugeny Rodichev 的有益讨论。欢迎提出意见和错误报告。

上一个  向上  下一个
F.21. lo — 管理大型对象  首页  F.23. pageinspect — 数据库页面的低级检查

提交更正

如果您发现文档中的任何内容不正确、与您对特定功能的体验不符或需要进一步澄清，请使用 此表单报告文档问题。