Using ltree for hierarchical structures in PostgreSQL
Hello my dear friends. In the previous article we learned about storing the tree structures in the RDBMS. In this article we will learn how to work with ltree module for PostgreSQL, which allow store data in a hierarchical tree-like structure.
What is ltree?
Ltree is a PostgreSQL module. It is implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided.
The ltree implements a materialized path, which very quick for INSERT/UPDATE/DELETE and pretty quick for SELECT operations
It will be generally faster than using a recursive CTE or recursive function that constantly needs to recalculate the branching
As built in query syntax and operators specifically designed for querying and navigating trees
First of all you should enable extension in your database. You can do this by this command:
Let’s create table and add to it some data:
Also we should add some indexes:
As you can see, I create table ‘comments’ with field ‘path’, which contain full path by tree for this comment. As you can see, for tree divider I use 4 numbers and dot.
Let’s found all comments, where path begin from ‘0001.0003’:
We can check this sql by EXPLAIN command:
Let’s for test disable seq scan:
Now it’s slower, but you can see, how it use index. First request use sequence scan because we have not many data in table.
We can do select “path <@ ‘0001.0003’” in another way:
Also you should not forget about ordering of data. Example:
Now with order:
There are several modifiers that can be put at the end of a non-star label in lquery to make it match more than just the exact match:
”@” - match case-insensitively, for example a@ matches A
”*” - match any label with this prefix, for example foo* matches foobar
”%” - match initial underscore-separated words
Also, you can write several possibly-modified labels separated with
(OR) to match any of those labels, and you can put ! (NOT) at the start to match any label that doesn’t match any of the alternatives. Example:
Now let’s use it for our commentable system. Find all direct childrens for parent ‘0001.0003’:
Find all childrens for parent ‘0001.0003’:
Find parent for children ‘0001.0003.0002.0002.0005’:
If you path will not be unique, it will get several records.
As can be seen, working with ltree materialized path is very simple. In this article, I have listed are not all the possible usage of ltree. It is not considered full-text search issues ltxtquery. But you can found this in documentation.
That’s all folks! Thank you for reading till the end.