在PostgreSQL中使用ltree处理层次结构数据的方法

2024-10-01 18:51:33

在本文中，我们将学习如何使用PostgreSQL的ltree模块，该模块允许以分层的树状结构存储数据。

什么是ltree？

Ltree是PostgreSQL模块。它实现了一种数据类型ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。

为什么选择ltree？

ltree实现了一个物化路径，对于INSERT / UPDATE / DELETE来说非常快，而对于SELECT操作则较快
通常，它比使用经常需要重新计算分支的递归CTE或递归函数要快
如内置的查询语法和专门用于查询和导航树的运算符
索引！！！

初始数据

首先，您应该在数据库中启用扩展。您可以通过以下命令执行此操作：

CREATE EXTENSION ltree;

让我们创建表并向其中添加一些数据：

CREATE TABLE comments (user_id integer, description text, path ltree);
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');
INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');

另外，我们应该添加一些索引：

CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);
CREATE INDEX path_comments_idx ON comments USING btree(path);

正如您看到的那样，我建立comments表时带有path字段，该字段包含该表的tree全部路径。如您所见，对于树分隔符，我使用4个数字和点。

让我们在commenets表中找到path以‘0001.0003'的记录：

$ SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
 user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  5 | 0001.0003.0002.0003
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
(12 rows)

让我们通过EXPLAIN命令检查这个SQL：

$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
            QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on comments (cost=0.00..1.24 rows=2 width=38) (actual time=0.013..0.017 rows=12 loops=1)
 Filter: (path <@ '0001.0003'::ltree)
 Rows Removed by Filter: 7
 Total runtime: 0.038 ms
(4 rows)

让我们禁用seq scan进行测试：

$ SET enable_seqscan=false;
SET
$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using path_gist_comments_idx on comments (cost=0.00..8.29 rows=2 width=38) (actual time=0.023..0.034 rows=12 loops=1)
 Index Cond: (path <@ '0001.0003'::ltree)
 Total runtime: 0.076 ms
(3 rows)

现在SQL慢了，但是能看到SQL是怎么使用index的。
第一个SQL语句使用了sequence scan，因为在表中没有太多的数据。

我们可以将select “path <@ ‘0001.0003'” 换种实现方法：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  5 | 0001.0003.0002.0003
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
(12 rows)

你不应该忘记数据的顺序，如下的例子：

$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0001');
$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0002');
$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0003');
$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  5 | 0001.0003.0002.0003
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
(15 rows)

现在进行排序：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  5 | 0001.0003.0002.0003
(15 rows)

可以在lquery的非星号标签的末尾添加几个修饰符，以使其比完全匹配更匹配：
“ @”-不区分大小写匹配，例如a @匹配A
“ *”-匹配任何带有该前缀的标签，例如foo *匹配foobar
“％”-匹配以下划线开头的单词

$ SELECT user_id, path FROM comments WHERE path ~ '0001.*{1,2}.0001|0002.*' ORDER by path;
 user_id |   path
---------+--------------------------
  2 | 0001.0001.0001
  2 | 0001.0001.0001.0001
  1 | 0001.0001.0001.0002
  5 | 0001.0001.0001.0003
  6 | 0001.0002.0001
  8 | 0001.0003.0001
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  5 | 0001.0003.0002.0003
(19 rows)

我们来为parent ‘0001.0003'找到所有直接的childrens，见下：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*{1}' ORDER by path;
 user_id |  path
---------+----------------
  8 | 0001.0003.0001
  9 | 0001.0003.0002
(2 rows)

为parent ‘0001.0003'找到所有的childrens，见下：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  5 | 0001.0003.0002.0003
(15 rows)

为children ‘0001.0003.0002.0002.0005'找到parent:

$ SELECT user_id, path FROM comments WHERE path = subpath('0001.0003.0002.0002.0005', 0, -1) ORDER by path;
 user_id |  path
---------+---------------------
  2 | 0001.0003.0002.0002
(1 row)

如果你的路径不是唯一的，你会得到多条记录。

概述

可以看出，使用ltree的物化路径非常简单。在本文中，我没有列出ltree的所有可能用法。它不被视为全文搜索问题ltxtquery。但是您可以在PostgreSQL官方文档(http://www.postgresql.org/docs/current/static/ltree.html)中找到它。

了解更多PostgreSQL热点资讯、新闻动态、精彩活动，请访问中国PostgreSQL官方网站：www.postgresqlchina.com

解决更多PostgreSQL相关知识、技术、工作问题，请访问中国PostgreSQL官方问答社区：www.pgfans.cn

下载更多PostgreSQL相关资料、工具、插件问题，请访问中国PostgreSQL官方下载网站：www.postgreshub.cn

到此这篇关于在PostgreSQL中使用ltree处理层次结构数据的文章就介绍到这了,更多相关PostgreSQL层次结构数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

postgreSQL数据库默认用户postgres常用命令分享

1.修改用户postgres的密码 #alter user postgres with password 'xxxx';(其中xxxx是修改的密码). 2.查看下当前schema的所有者: // 查看当前schema的所有者,相当于\du元命令 SELECT n.nspname AS "Name", pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHE
SpringBoot连接使用PostgreSql数据库的方法

一.介绍此次更新时间:2020-10-28,现在是上班时间,偷更一下.其实使用IDEA的话无需配置Maven什么的,如果你们公司不是强制要求使用Eclipse的话,只需要有个JDK的环境即可,IDEA自带了一个版本的Maven,还是挺新的,目前IDEA最新版2.2.3的版本.我们也不用按照下面这个步骤去下载Spring Initializr,我们在IDEA中新建项目选择到Maven就行了,干净简洁. 目前在Resources目录下的application大多数是使用yml语法了.现在已经太长时
postgresql中的ltree类型使用方法

postgresql有很多比较妖的数据类型,ltree算一个. 简介 ltree是Postgresql的一个扩展类型 http://www.sai.msu.su/~megera...,在解决树形结构的数据存储上使用. 查看是否安装了插件 select * from pg_extension where extname = 'ltree'; 定义字段的格式为:L1.L2.L3.L4.L5.L6..... 标签是一系列字母数字字符和下划线A-Za-z0-9_, 标签长度必须小于256个字节.标签路
PostgreSQL 恢复误删数据的操作

在Oracle中:删除表或者误删表记录:有个闪回特性,不需要停机操作,可以完美找回记录.当然也有一些其他的恢复工具:例如odu工具,gdul工具.都可以找回数据.而PostgreSQL目前没有闪回特性.如何在不停机情况下恢复误删数据.还好是有完整的热备份. 本文描述的方法是:利用热备份在另一台服务器进行数据恢复:再导入正式环境:这样不影响数据库操作.这方法也适用在Oracle恢复.必须满足几个条件 1.有完整的基础数据文件备份和归档文件备份.所以备份是很重要的. 2.有一台装好同款Postgre
在PostgreSQL中使用ltree处理层次结构数据的方法

在本文中,我们将学习如何使用PostgreSQL的ltree模块,该模块允许以分层的树状结构存储数据. 什么是ltree? Ltree是PostgreSQL模块.它实现了一种数据类型ltree,用于表示存储在分层树状结构中的数据的标签.提供了用于搜索标签树的广泛工具. 为什么选择ltree? ltree实现了一个物化路径,对于INSERT / UPDATE / DELETE来说非常快,而对于SELECT操作则较快通常,它比使用经常需要重新计算分支的递归CTE或递归函数要快如内置的查询语法和专
解析WPF绑定层次结构数据的应用详解

在实际项目应用中会存在多种类型的层次结构数据,WPF提供了良好的数据绑定机制.其中运用最频繁的就是ListBox和TreeView控件. 一.ListBox和TreeView控件的区别1.ListBox显示单层次数据集合,TreeView可以显示单层次和多层次数据集合:2.通过ListBox在UI层面可以展示良好的数据显示效果,对数据集合可以进行排序.分组.过滤操作:3.TreeView显示为一个多层次的数据集合为树形结构,通过Templete和Style属性同样可以为其定义良好的数据显示效果:
PostgreSQL中常用的时间日期脚本使用教程

获取系统时间函数 select now(); --2013-11-28 16:20:25.259715+08 select current_timestamp; --2013-11-28 16:20:38.815466+08 select current_date; --2013-11-28 select current_time; --16:21:08.981171+08 时间的计算 --使用interval select now()+interval '2 day'; --2013-11-3
PHP 读取Postgresql中的数组

复制代码代码如下: function getarray_postgresql($arraystr) { $regx1 = '/^{(.*)}$/'; $regx2 = "/\"((\\\\\\\\|\\\\\"|[^\"])+)\"|[^,]+/"; $regx3 = '/^[^"].*$|^"(.*)"$/'; $match = null; preg_match( $reg
在PostgreSQL中使用数组时值得注意的一些地方

在Heap中,我们依靠PostgreSQL支撑大多数后端繁重的任务,我们存储每个事件为一个hstore blob,我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组,并将这些事件按时间排序. Hstore能够让我们以灵活的方式附加属性到事件中,而且事件数组赋予了我们强大的性能,特别是对于漏斗查询,在这些查询中我们计算不同转化渠道步骤间的输出. 在这篇文章中,我们看看那些意外接受大量输入的PostgreSQL函数,然后以高效,惯用的方式重写它. 你的第一反应可能是将PostgreSQ
在PostgreSQL中使用日期类型时一些需要注意的地方

当我们这些使用Rails的人看到例如5.weeks.from_nowor3.days.ago + 2.hours时并不会感到惊讶.同样,PostgreSQL也可以做到,你可以通过简单调用PostgreSQL内置函数来实现相同的功能. 当前时间/日期/时间戳获取当前时间的方式有很多种,在这之前我们需要知道以下两种类型的区别: 总是返回当前的值 (clock_timestamp()) 总是返回当前值,但在事务中它返回的是事务开始的时间(now()) 让我们看下面这个例子 postgres=# BE
PostgreSQL中Slony-I同步复制部署教程

前言本文主要介绍了关于PostgreSQL中Slony-I同步复制部署的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧本次测试环境 IP 10.189.102.118 10.189.100.195 10.189.100.226 PGHOME /usr/local/pgsql /usr/local/pgsql /usr/local/pgsql Role Origin,Providers Subscribers,Providers Subscribers Databas
PostgreSQL 中字段类型varchar的用法

PostgreSql数据库中varchar类型与sql server中字段用法有差别,PostgreSql中如果字段设置为varchar类型长度为10,则无论存字母.数字或其它符号,长度最大为10个,也就是字母和汉字占的位置是一样的. Sql server中如设置字段类型为nvarchar类型长度为10,则存汉字最大为5个,字母为10个,字母加汉字混合时,字母和汉字占的长度一样补充:Postgresql 数据库 varchar()字符占用多少字节如下所示: create table tmp1
postgresql中wal_level的三个参数用法说明

wal_level中有三个主要的参数:minimal.archive和hot_standby 1.minimal是默认的值,它仅写入崩溃或者突发关机时所需要的信息(不建议使用). 2.archive是增加wal归档所需的日志(最常用). 3.hot_standby是在备用服务器上增加了运行只读查询所需的信息,一般实在流复制的时候使用到. 补充:postgresql WAL相关参数配置文件 # - Settings - wal_level = minimal # minimal, replica

在PostgreSQL中使用ltree处理层次结构数据的方法

什么是ltree？

为什么选择ltree？

初始数据

概述

相关推荐

随机推荐