最近一个月使用Postgresql的时候，经常遇到ltree的数据，感觉有些别扭，可是有绕不过去。今天决心整理一下，以后使用方便一些。
ltree是Postgresql的一个扩展类型，由两位国外PostgreSQL贡献者共同开发。网址如下：http://www.sai.msu.su/~megera/postgres/gist
开发这个类型的目的是为了解决复杂的树状模型相关问题，如图：
也是这个网址提到的内容：http://blog.163.com/digoal@126/blog/static/163877040201132843255911/
执行sql：select * from pg_extension where extname = ‘ltree‘;
extname | ltree extowner | 10 extnamespace | 2200 extrelocatable | t extversion | 1.0 extconfig | extcondition |
如果没有安装，参考网站：http://blog.163.com/digoal@126/blog/static/1638770402011427104710922/
ltree是由标签和分隔符组成的字符串，比如：L1.L2.L3
select * from mirror.sight where area_path <@ (select area_path from mirror.sight where name = ‘加利福尼亚州‘) and type = ‘城市‘
--在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‘);
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)
select subltree(song,3,4) from test.test where subltree(song,2,3) = ‘LiuDeHua‘; ******************************************** subltree --------------- AiNiYiWanNian WangQinShui JinTian
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
select subltree(song,3,4) from test.test where subltree(song,0,1) = ‘GangTai‘;
***********************
subltree
----------------
AiNiYiWanNian
WangQinShui
QingShu
WenBie
MeiFeiSeWu
ZhongShenMeiLi
JinTian
(7 rows)
原文：https://www.cnblogs.com/actively/p/13222498.html