Shulou(Shulou.com)06/01 Report--

Editor to share with you how POSTGRESQL stores tree data and deals with tree data. I hope you will get something after reading this article. Let's discuss it together.

Tree data is what kind of embodiment, form, here first show in advance, for the following postgresql operation tree data to do a groundwork.

The way such data is stored in POSTGRESQL and the way data is processed are the problems to be discussed and solved today.

The figure above uses a new field type, ltree. Ltree itself uses a tree structure to store data. After we enter the data, we can start to query. For example, we want to know that the current tree structure contains the tree path of A.B through the operation symbol of LTREE itself.

Using the above statement as a window for the table path_tree, for the path A.B and containing this path, you can see from the input data that the two paths contain or equal to 'A.B'.

The above figure means that the path contains A.E and above.

After a general understanding of the operation of ltree, we go back to what ltree is and what ltree can do.

For example, we want to know how many paths are through the A.E node.

Select * from path_tree where path ~ 'A.E.'

Other common SQL methods are also supported and can be queried.

Currently, the LTREE types supported by PG12 are numbers and letters, and are less than 256 bytes.

Ltree is a PostgreSQL module. It implements a data type ltree that represents data labels stored in a hierarchical tree structure. Provides a wide range of tools for searching through tag trees. Pass the point. To effectively segment the data and turn it into a combinable tree-like hierarchical expression through the combination of data and points. And provide complex query expression, to extract a function of data.

For this type of data, indexing supports two types

1 B +

2 GIST

GIST supports relatively more operands, so it is recommended that you use GIST to create indexes on data of type LTREE.

In fact, this data type can also help us to complete some interesting statistical functions. If we now have a string of letters from A to M, or numbers, we need to find the path sorting from An or any letter to M or any other alphabetical order (which must be in order and not greater than picking letters in front of or equal to any alphabetical position), and how many possibilities are there.

Create a table first

CREATE TABLE t_orga

(

Parent_code text

Child_code text

UNIQUE (parent_code, child_code)

);

Then we create a materialized view and integrate the above data through materialized view recursion, because the first row has NULL, so we need UNION ALL to integrate the NULL and the data without NULL.

You will notice that although the LTREE type is not used in the above table, the ltree type is actually used in the materialized view, otherwise it will not be queried in the form of LTREE.

At the same time, the ltree field in the atomized view can also be indexed to improve the efficiency of data query.

After reading this article, I believe you have a certain understanding of "how to store and deal with tree data in POSTGRESQL". If you want to know more about it, please follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.