postgresql中的ltree类型使用方法

select * from pg_extension where extname = 'ltree';

create table tmp.ltree_table(name varchar, path ltree);

insert into tmp.ltree_table values ('小明','北京.东城.小明');
insert into tmp.ltree_table values ('小刚','北京.西城.小刚');
insert into tmp.ltree_table values ('小红','北京.南城.小红');
insert into tmp.ltree_table values ('小小明','北京.东城.小明.小小明');
insert into tmp.ltree_table values ('小明明','北京.东城.小明.小明明');
insert into tmp.ltree_table values ('小小红','北京.东城.小红.小小红');
insert into tmp.ltree_table values ('小红红','北京.东城.小红.小红红');

select c.name, s.name from tmp.ltree_table s join (select path ,name from tmp.ltree_table ) c on s.path <@ c.path and s.name <> c.name;

select subltree(path,1,2) from tmp.ltree_table;

select distinct s.name from tmp.ltree_table s where subltree(s.path,1,2) = '南城';

select s.name,nlevel(s.path) from tmp.ltree_table s;