postgresql ltree类型

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

一、简介

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

二、目的

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

postgresql ltree类型

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

三、详解

3.1 是否安装ltree

  执行sql:select * from pg_extension where extname = ‘ltree‘;

postgresql ltree类型
extname        | ltree
extowner       | 10
extnamespace   | 2200
extrelocatable | t
extversion     | 1.0
extconfig      | 
extcondition   | 
postgresql ltree类型

  如果没有安装,参考网站: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 建立数据表 

postgresql ltree类型
--在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‘);
postgresql ltree类型

postgresql ltree类型

5.2 查看信息

5.2.0 数据列表信息

postgresql ltree类型
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)
postgresql ltree类型

5.2.1 查看刘德华的所有歌曲

postgresql ltree类型
select subltree(song,3,4) from test.test where subltree(song,2,3) = ‘LiuDeHua‘;
********************************************
   subltree    
---------------
 AiNiYiWanNian
 WangQinShui
 JinTian
postgresql ltree类型

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

postgresql ltree类型
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
postgresql ltree类型

5.2.3 查看港台的所有歌曲

postgresql ltree类型
select subltree(song,3,4) from test.test where subltree(song,0,1) = ‘GangTai‘;
***********************
    subltree    
----------------
 AiNiYiWanNian
 WangQinShui
 QingShu
 WenBie
 MeiFeiSeWu
 ZhongShenMeiLi
 JinTian
(7 rows)
postgresql ltree类型

postgresql ltree类型

上一篇:python知识总结(魔法方法、with上下文管理、pymysql等)


下一篇:使用python操作MySQL数据库