在PostgreSQL中使用ltree處理層次結構數(shù)據(jù)的方法
更新時間：2021年03月30日 11:37:29 作者：PostgreSQLChina
這篇文章主要介紹了在PostgreSQL中使用ltree處理層次結構數(shù)據(jù),本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
在本文中，我們將學習如何使用PostgreSQL的ltree模塊，該模塊允許以分層的樹狀結構存儲數(shù)據(jù)。
什么是ltree？
Ltree是PostgreSQL模塊。它實現(xiàn)了一種數(shù)據(jù)類型ltree，用于表示存儲在分層樹狀結構中的數(shù)據(jù)的標簽。提供了用于搜索標簽樹的廣泛工具。
為什么選擇ltree？
- ltree實現(xiàn)了一個物化路徑，對于INSERT / UPDATE / DELETE來說非?？?，而對于SELECT操作則較快
- 通常，它比使用經(jīng)常需要重新計算分支的遞歸CTE或遞歸函數(shù)要快
- 如內置的查詢語法和專門用于查詢和導航樹的運算符
- 索引?。?！
初始數(shù)據(jù)
首先，您應該在數(shù)據(jù)庫中啟用擴展。您可以通過以下命令執(zhí)行此操作：
CREATE EXTENSION ltree;
讓我們創(chuàng)建表并向其中添加一些數(shù)據(jù)：
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個數(shù)字和點。
讓我們在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)
現(xiàn)在SQL慢了，但是能看到SQL是怎么使用index的。
第一個SQL語句使用了sequence scan，因為在表中沒有太多的數(shù)據(jù)。
我們可以將select “path <@ ‘0001.0003'” 換種實現(xiàn)方法：
$ 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)
你不應該忘記數(shù)據(jù)的順序，如下的例子：
$ 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)
現(xiàn)在進行排序：
$ 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的非星號標簽的末尾添加幾個修飾符，以使其比完全匹配更匹配：
“ @”-不區(qū)分大小寫匹配，例如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熱點資訊、新聞動態(tài)、精彩活動，請訪問中國PostgreSQL官方網(wǎng)站：www.postgresqlchina.com
解決更多PostgreSQL相關知識、技術、工作問題，請訪問中國PostgreSQL官方問答社區(qū)：www.pgfans.cn
下載更多PostgreSQL相關資料、工具、插件問題，請訪問中國PostgreSQL官方下載網(wǎng)站：www.postgreshub.cn
到此這篇關于在PostgreSQL中使用ltree處理層次結構數(shù)據(jù)的文章就介紹到這了,更多相關PostgreSQL層次結構數(shù)據(jù)內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
本文給大家介紹了PostgreSQL如何使用UUID,離線安裝時,一般有四個包,都安裝的話,只需要開啟uuid的使用即可,如果工具包(即 postgresql11-contrib）沒有安裝的話,需要單獨安裝一次,再進行開啟,需要的朋友可以參考下2024-02-02
這篇文章主要介紹了postgresql使用dblink跨庫增刪改查，本文給大家介紹的非常詳細對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-04-04
文章介紹了在數(shù)據(jù)庫中處理日期和時間類型時的一些注意事項,包括如何將字符串轉換為日期或時間類型,以及在比較時自動轉換的情況,作者建議在使用數(shù)據(jù)庫時,根據(jù)具體情況進行適當?shù)念愋娃D換,以避免潛在的錯誤2025-01-01
15個postgresql數(shù)據(jù)庫實用命令分享
這篇文章主要介紹了15個實用的postgresql數(shù)據(jù)庫命令分享,都是一些技巧性的postgresql命令,需要的朋友可以參考下2014-07-07
PostgreSQL 查找當前數(shù)據(jù)庫的所有表操作
這篇文章主要介紹了PostgreSQL 查找當前數(shù)據(jù)庫的所有表操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了postgresql 計算兩點距離的2種方法小結，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 自定義自動類型轉換操作(CAST)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql插入后返回id的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL數(shù)據(jù)庫字符串拼接、大小寫轉換以及substring詳解
在日常工作中會遇到將多行的值拼接為一個值展現(xiàn),下面這篇文章主要給大家介紹了關于PostgreSQL數(shù)據(jù)庫字符串拼接、大小寫轉換以及substring的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-04-04
CentOS 9 Stream 上安裝 PostgreSQL 16的步
在CentOS9Stream上安裝PostgreSQL16,首先添加PostgreSQL官方倉庫,然后禁用系統(tǒng)自帶PostgreSQL版本,避免沖突,使用dnf命令安裝PostgreSQL16,并初始化數(shù)據(jù)庫,本文給大家介紹CentOS 9 Stream 上安裝 PostgreSQL 16的步驟,感興趣的朋友一起看看吧2024-11-11