postgresql ltree类型

2022-09-28 21:47:08

最近一个月使用Postgresql的时候，经常遇到ltree的数据，感觉有些别扭，可是有绕不过去。今天决心整理一下，以后使用方便一些。

一、简介

ltree是Postgresql的一个扩展类型，由两位国外PostgreSQL贡献者共同开发。网址如下：http://www.sai.msu.su/~megera/postgres/gist

二、目的

开发这个类型的目的是为了解决复杂的树状模型相关问题，如图：

也是这个网址提到的内容：http://blog.163.com/digoal@126/blog/static/163877040201132843255911/

三、详解

3.1 是否安装ltree

执行sql：select * from pg_extension where extname = 'ltree';

extname        | ltree

extowner       |

extnamespace   |

extrelocatable | t

extversion     | 1.0

extconfig      |

extcondition   |

如果没有安装，参考网站：http://blog.163.com/digoal@126/blog/static/1638770402011427104710922/

3.2 定义

ltree是由标签和分隔符组成的字符串，比如：L1.L2.L3

分隔符：一般是小数点
标签：目前支持由A-Za-z0-9_组成字符串
路径：标签本身也是路径
lquery：可以理解为一个搜索ltree的正则表达式，例如：*.test.*，表示任何包含test标签的路径
ltxtquery：同上，一般用于全文扫描。

四、操作符

ltree @> ltree 左手边参数是右手边的祖先（或者平辈）吗？

select * from mirror.sight where  area_path <@ (select area_path from mirror.sight where name = '加利福尼亚州') and type = '城市'

postgresql ltree类型

ltree <@ ltree 左手边参数是右手边的后代（或者平辈）吗？
ltree ~ lquery ltree 匹配 lquery 吗？
lquery ~ ltree ltree 匹配 lquery 吗？

五、事例

5.1 建立数据表

--在test的schema下面建立测试表

create table test.test(id serial,song ltree not null);

--添加音乐信息

insert into test.test (song) values ('GangTai.NanGeShou.LiuDeHua.AiNiYiWanNian');

insert into test.test (song) values ('GangTai.NanGeShou.LiuDeHua.JinTian');

insert into test.test (song) values ('GangTai.NanGeShou.LiuDeHua.WangQinShui');

insert into test.test (song) values ('GangTai.NanGeShou.ZhangXueYou.QingShu');

insert into test.test (song) values ('GangTai.NanGeShou.ZhangXueYou.WenBie');

insert into test.test (song) values ('GangTai.NvGeShou.ZhenXiuWen.MeiFeiSeWu');

insert into test.test (song) values ('GangTai.NvGeShou.ZhenXiuWen.ZhongShenMeiLi');

insert into test.test (song) values ('DaLu.NanGeShou.DaoLang.2002NianDeDiYiChangXue');

insert into test.test (song) values ('DaLu.NvGeShou.FanBinBin.FeiNiao');

5.2 查看信息

5.2.0 数据列表信息

select * from test.test;

**********************************************

 id |                     song

----+-----------------------------------------------

  1 | GangTai.NanGeShou.LiuDeHua.AiNiYiWanNian

  2 | GangTai.NanGeShou.LiuDeHua.WangQinShui

  3 | GangTai.NanGeShou.ZhangXueYou.QingShu

  4 | GangTai.NanGeShou.ZhangXueYou.WenBie

  5 | GangTai.NvGeShou.ZhenXiuWen.MeiFeiSeWu

  6 | GangTai.NvGeShou.ZhenXiuWen.ZhongShenMeiLi

  7 | DaLu.NanGeShou.DaoLang.2002NianDeDiYiChangXue

  8 | DaLu.NvGeShou.FanBinBin.FeiNiao

  9 | GangTai.NanGeShou.LiuDehua.JinTian

(9 rows)

5.2.1 查看刘德华的所有歌曲

select subltree(song,3,4) from test.test where subltree(song,2,3) = 'LiuDeHua';

********************************************

   subltree

---------------

 AiNiYiWanNian

 WangQinShui

 JinTian

5.2.2 查看与刘德华同一个区域（港台）的歌手

select distinct subltree(song,2,3) 
from test.test

where song <@ (select subpath(song,0,2) from test.test where subltree(song,2,3)='LiuDeHua' limit 1);

**********************************

  subltree

-------------

 LiuDeHua

 ZhangXueYou

5.2.3 查看港台的所有歌曲

select subltree(song,3,4) from test.test where subltree(song,0,1) = 'GangTai';

***********************

    subltree

----------------

 AiNiYiWanNian

 WangQinShui

 QingShu

 WenBie

 MeiFeiSeWu

 ZhongShenMeiLi

 JinTian

(7 rows)

码农公寓