参考链接
树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type（德哥github博文）
1. 安装、数据类型介绍、操作符介绍、函数介绍
安装 "ltree" 索引扩展(使用超级用户)
create extension ltree;
查看
select * from pg_extension ; \dT
数据类型介绍
-
ltree (目前只支持A-Z,a-z,0-9,_作为label的合法字符)
树形结构类型,一个ltree被称为一个path,由1或多个LABEL组成,每个label由A-Za-z0-9_组成。ltree是由标签和分隔符组成的字符串，比如：L1.L2.L3
-
lquery
规则表达式,用于匹配ltree类型.
具体参考手册,需要注意的是%匹配的不是一个label,而是label里的一个单词(_为单词分隔符
-
ltxtquery
一般用于全文扫描，注意，只有ltxtquery类型是符号和匹配的内容是可以有空格隔开的,lquery和ltree不支持空格。
操作符介绍:
| Operator | Returns | Description |
|---|---|---|
| ltree @> ltree | boolean | is left argument an ancestor of right (or equal)? |
| ltree <@ ltree | boolean | is left argument a descendant of right (or equal)? |
| ltree ~ lquery | boolean | does ltree match lquery? |
| lquery ~ ltree | boolean | does ltree match lquery? |
| ltree ? lquery[] | boolean | does ltree match any lquery in array? |
| lquery[] ? ltree | boolean | does ltree match any lquery in array? |
| ltree @ ltxtquery | boolean | does ltree match ltxtquery? |
| ltxtquery @ ltree | boolean | does ltree match ltxtquery? |
| ltree || ltree | ltree | concatenate ltree paths |
| ltree || text | ltree | convert text to ltree and concatenate |
| text || ltree | ltree | convert text to ltree and concatenate |
| ltree[] @> ltree | boolean | does array contain an ancestor of ltree? |
| ltree <@ ltree[] | boolean | does array contain an ancestor of ltree? |
| ltree[] <@ ltree | boolean | does array contain a descendant of ltree? |
| ltree @> ltree[] | boolean | does array contain a descendant of ltree? |
| ltree[] ~ lquery | boolean | does array contain any path matching lquery? |
| lquery ~ ltree[] | boolean | does array contain any path matching lquery? |
| ltree[] ? lquery[] | boolean | does ltree array contain any path matching any lquery? |
| lquery[] ? ltree[] | boolean | does ltree array contain any path matching any lquery? |
| ltree[] @ ltxtquery | boolean | does array contain any path matching ltxtquery? |
| ltxtquery @ ltree[] | boolean | does array contain any path matching ltxtquery? |
| ltree[] ?@> ltree | ltree | first array entry that is an ancestor of ltree; NULL if none |
| ltree[] ?<@ ltree | ltree | first array entry that is a descendant of ltree; NULL if none |
| ltree[] ?~ lquery | ltree | first array entry that matches lquery; NULL if none |
| ltree[] ?@ ltxtquery | ltree | first array entry that matches ltxtquery; NULL if none |
函数简单介绍:
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| subltree(ltree,int start,int end) | ltree | subpath of ltree from position start to position end-1 (counting from 0) | subltree('Top.Child1.Child2',1,2) | Child1 |
| subpath(ltree,int offset,int len) | ltree | subpath of ltree starting at position offset,length len. If offset is negative,subpath starts that far from the end of the path. If len is negative,leaves that many labels off the end of the path. | subpath('Top.Child1.Child2',2) | Top.Child1 |
| subpath(ltree,int offset) | ltree | subpath of ltree starting at position offset,extending to end of path. If offset is negative,subpath starts that far from the end of the path. | subpath('Top.Child1.Child2',1) | Child1.Child2 |
| nlevel(ltree) | integer | number of labels in path | nlevel('Top.Child1.Child2') | 3 |
| index(ltree a,ltree b) | integer | position of first occurrence of b in a; -1 if not found | index('0.1.2.3.5.4.5.6.8.5.6.8','5.6') | 6 |
| index(ltree a,ltree b,int offset) | integer | position of first occurrence of b in a,searching starting at offset; negative offsetmeans start -offset labels from the end of the path | index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4) | 9 |
| text2ltree(text) | ltree | cast text to ltree | - | - |
| ltree2text(ltree) | text | cast ltree to text | - | - |
| lca(ltree,ltree,...) | ltree | lowest common ancestor,i.e.,longest common prefix of paths (up to 8 arguments supported) | lca('1.2.2.3','1.2.3.4.5.6') | 1.2 |
| lca(ltree[]) | ltree | lowest common ancestor,longest common prefix of paths | lca(array['1.2.2.3'::ltree,'1.2.3']) | 1.2 |
2. 样例
2.1 推酷、德哥博客都用到的
建表
create table public.test( id serial,song ltree not null);
初始化后的数据
- 查询：刘德华的歌曲
- 查询：与刘德华同一个区域（港台，男歌手）的歌手