• postgresql 10 ltree 使用说明

    官方文档 <a href='https://www.postgresql.org/docs/10/static/ltree.html' target=_blank'>https://www.postgresql.org/docs/10/static/ltree.html

    ltree是俄罗斯Teodor Sigaev和Oleg Bartunov共同开发的PostgreSQL contrib扩展模块，它包含数据类型的实现、为树状结构组织的数据提供索引和查询。 http://www.sai.msu.su/~megera/postgres/gist/

    关于GIST索引的一些介绍 https://yq.aliyun.com/articles/68244

    一、安装ltree

    CREATE EXTENSION IF NOT EXISTS ltree;

    检查是否安装成功

    select * from pg_extension where extname = 'ltree';

    二、ltree的定义

    1. 标签是由一组字符数据（A-Za-z0-9_）组成的，每个标签最大256字节
    2. 标签的路径是由0个或多个点号分割(只能是.号)，如 a.b.c.d
    3. 标签的总长度必须小于65kb，但最好维持在2kb以下

    ltree提供两种数据类型

    1. ltree：存储标签路径
    2. lquery：表示用于匹配ltree值的类似正则表达式的模式。 一个简单的单词与路径中的标签匹配。 星号（*）匹配零个或多个标签。 例如：

    foo 匹配确切的标签路径foo
    *.foo.* 匹配包含标签foo的任何标签路径
    *.foo 匹配最后一个标签为foo的任何标签路径

    *标识可以量化，类似正则表达式

    * {n}完全匹配n个标签
    * {n，}匹配至少n个标签
    * {n，m}匹配至少n个但不超过m个标签
    * {，m}匹配最多m个标签 - 与* {0，m}相同

    有几个修饰符可以放在lquery中非星形标签的末尾，以使其匹配的不仅仅是完全匹配：

    @ 匹配大小写不敏感，例如@匹配A.
    * 匹配任何带有此前缀的标签，例如foo *匹配foobar
    ％ 匹配初始下划线分隔的单词

    三、创建表&测试数据

    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');

    四、安装GIST和BTREE索引

    CREATE INDEX path_gist_idx ON test USING gist(path);
CREATE INDEX path_idx ON test USING btree(path);

    五、操作符

    操作符 返回类型 描述
    tree @> ltree boolean 左参数是右边的祖先（或平等）？
    tree <@ ltree boolean 左参数是右（或相等）的后代？
    tree ~ lquery boolean ltree是否匹配lquery？
    query ~ ltree boolean ltree是否匹配lquery？
    tree ? lquery[] boolean ltree是否匹配数组中的任何lquery？
    query[] ? ltree boolean ltree是否匹配数组中的任何lquery？
    tree @ ltxtquery boolean ltree是否匹配ltxtquery？
    txtquery @ ltree boolean ltree是否匹配ltxtquery？
    tree ltree
    tree text
    ext ltree
    tree[] @> ltree boolean 数组是否包含ltree的祖先？
    tree <@ ltree[] boolean 数组是否包含ltree的祖先？
    tree[] <@ ltree boolean 数组是否包含ltree的后代？
    tree @> ltree[] boolean 数组是否包含ltree的后代？
    tree[] ~ lquery boolean 数组包含任何匹配lquery的路径？
    query ~ ltree[] boolean 数组包含任何匹配lquery的路径？
    tree[] ? lquery[] boolean ltree数组是否包含任何匹配任何lquery的路径？
    query[] ? ltree[] boolean ltree数组是否包含任何匹配任何lquery的路径？
    tree[] @ ltxtquery boolean 数组包含任何匹配ltxtquery的路径？
    txtquery @ ltree[] boolean 数组包含任何匹配ltxtquery的路径？
    tree[] ?@> ltree ltree 第一个数组条目，是ltree的祖先;如果没有则为NULL
    tree[] ?<@ ltree ltree 第一个数组条目，是ltree的后代;如果没有则为NULL
    tree[] ?~ lquery ltree 第一个匹配lquery的数组条目;如果没有则为NULL
    tree[] ?@ ltxtquery ltree 第一个匹配ltxtquery的数组条目;如果没有则为NULL

    六、ltree函数库及示例

    执行结果部分全部都亲测过，可以参考下。

    函数名 返回类型 描述 示例 执行结果
    subltree(ltree, int start, int end) ltree ltree寻找子路径，下标从start到end的
    （从0开始计数）,下标越界会出错    		 select subltree('a.b.c.d', -1,0);
    select subltree('a.b.c.d', 0,1);
    select subltree('a.b.c.d', 2,3);
    select subltree('a.b.c.d', 4,5);
    select subltree('a.b.c.d', 2,1)    		 invalid positions
    a
    c
    invalid positions
    invalid positions
    subpath(ltree, int offset, int len) ltree ltree根据偏移量offset和len寻找子路径，
    如果offset为负则从路径末端开始。
    如果len为负数，则将许多标签留在路径的末尾，
    ps:len为负数的最好少用,有点奇怪    		 select subpath('a.b.c.d', 0,2);
    select subpath('a.b.c.d', 0,9);
    select subpath('a.b.c.d', 1,2);
    select subpath('a.b.c.d', 4,1);
    select subpath('a.b.c.d', -1,1);
    select subpath('a.b.c.d', -1,2);
    select subpath('a.b.c.d', -2,1);
    select subpath('a.b.c.d', -2,2);
    select subpath('a.b.c.d', -2,9);
    select subpath('a.b.c.d', -3,2);    		 a,b
    a,b,c,d
    b,c
    invalid positions
    d
    d
    c
    c,d
    c,d
    b,c
    subpath(ltree, int offset) ltree ltree根据偏移量offset寻的子路径，
    延伸到路径的末尾，如果offset为负，
    则子路径从路径末端开始。    		 select subpath('a.b.c.d', 0);
    select subpath('a.b.c.d', 1);
    select subpath('a.b.c.d', 2);
    select subpath('a.b.c.d', 3);
    select subpath('a.b.c.d', 4);
    select subpath('a.b.c.d', -1);
    select subpath('a.b.c.d', -2);
    select subpath('a.b.c.d', -3);
    select subpath('a.b.c.d', -4);
    select subpath('a.b.c.d', -5);    		 a,b,c,d
    b,c,d
    c,d
    d
    invalid positions
    d
    c,d
    b,c,d
    a,b,c,d
    d
    nlevel(ltree) integer 计算路径中标签数量 select nlevel('');
    select nlevel('a');
    select nlevel('a.b');
    select nlevel('a.b.c.d');    		 0
    1
    2
    4
    index(ltree a, ltree b) integer 计算第一次在a中出现b的位置，
    没找到返回-1    		 select index('a.b.c.d','c.d');
    select index('a.b.c.d','a');
    select index('a.b.c.d','a,b');
    select index('a.b.c.d','e');    		 2
    0
    0
    -1
    index(ltree a, ltree b, int offset) integer 计算在a中第一次出现b的位置，
    从偏移处开始搜索;
    负偏移意味着从路径末端开始    		 select index('a.b.c.d.e','c.d',1);
    select index('a.b.c.d.e','c.d',2);
    select index('a.b.c.d.e','c.d',3);
    select index('a.b.c.d.e','c.d',-1);
    select index('a.b.c.d.e','c.d',-2);
    select index('a.b.c.d.e','c.d',-3);    		 2
    2
    -1
    -1
    -1
    2
    text2ltree(text) ltree text 转 ltree
    ltree2text(ltree) text ltree 转 text
    lca(ltree, ltree, ...) ltree 路径最长的公共前缀部分
    （最多支持8个ltree参数）    		 select lca('a.b.c.d','a.b.c');
    select lca('a.b.c.d','a.b.c','a.b');
    select lca('a.b.c.d','c.d');    		 a.b
    a
    ''
    lca(ltree[]) ltree 路径最长的公共前缀部分 lca(array['1.2.2.3'::ltree,'1.2.3']) 1.2

    七、查询实例

    1.查找Top.Science开始的path

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

    2.查找包含Astronomy的path

    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)

    3.查找包含Astronomy且前面节点不含pictures的path

    SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

    4.查找第3个节点是'Astronomy'的，
    注意此处最好加上nlevel(path) >2，因为可能有数据没有这么多节点
    且where条件nlevel(path) >2要放在前面，postgresql的where条件是从前往后执行的

    select  path from test where  nlevel(path) >2 and subpath(path,2,1) ='Astronomy';
                path
------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)

    错误示范：

    select  path from test where subpath(path,2,1) ='Science' and nlevel(path) >1;
select  path from test where subpath(path,2,1) ='Science';
------------------------------------
ERROR:  invalid positions
SQL state: 22023

    5.查到第二个节点是Science，注意看nlevel的变量

    select  path from test where nlevel(path) >1 and subpath(path,1,1) ='Science' ;
------------------------------------
Top.Science
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(4 rows)

select  path from test where nlevel(path) >2 and subpath(path,1,1) ='Science' ;
------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)

    基本的使用案例就到此，其他的自己可以多去试试。ltree还是非常适合省市县级联这种关系
  • 相关阅读:
    直线方程和直线系方程
    多个参数之和积的取值范围
    解析几何习题
    React之Perf
    一文看懂npm、yarn、pnpm之间的区别
    Cannot read property 'properties' of undefined
    为什么要用PolyFill（JS中的修补匠）
    es6-promise
    原生js实现each方法
    有趣的js获取input标签中光标的索引
  • 原文地址：https://www.cnblogs.com/nickchou/p/9391904.html
Copyright © 2020-2023  润新知