PostgreSQL recursive query for Tree Structure Query

PostgreSQL recursive query for Tree Structure Query

A very interesting feature found during PostgreSQL's use is that recursive queries can be used for results that require a tree-like structure. For example, the data structure of common company departments is similar to the following SQL statement when we design the table structure. If parent_id is NULL, it indicates the top-level node. Otherwise, it indicates the ID of the parent node.

Create table department (
Id integer primary key,
Name varchar (32 ),
PARENT_ID integer references department (ID)
);

Below we create several pieces of test data

Insert into department (ID, NAME, PARENT_ID) VALUES (1, 'department _ 1', NULL );
Insert into department (ID, NAME, PARENT_ID) VALUES (11, 'department _ 11', 1 );
Insert into department (ID, NAME, PARENT_ID) VALUES (12, 'department _ 12', 1 );
Insert into department (ID, NAME, PARENT_ID) VALUES (111, 'department _ 000000', 11 );
Insert into department (ID, NAME, PARENT_ID) VALUES (121, 'department _ 000000', 12 );
Insert into department (ID, NAME, PARENT_ID) VALUES (122, 'department _ 000000', 12 );

Where
-DEPARTMENT_1 is a top-level node with two subnodes: DEPARTMENT_11 and DEPARTMENT_12.
-The DEPARTMENT_11 node has another subnode, DEPARTMENT_111.
-The DEPARTMENT_12 node has two subnodes: DEPARTMENT_121 and DEPARTMENT_122.

The following statements are used to generate a tree structure for Recursive queries:

With recursive t (ID, NAME, PARENT_ID, PATH, DEPTH) (
Select id, NAME, PARENT_ID, ARRAY [ID] as path, 1 AS DEPTH
FROM DEPARTMENT
WHERE PARENT_ID IS NULL

UNION ALL

Select d. ID, D. NAME, D. PARENT_ID, T. PATH | D. ID, T. DEPTH + 1 AS DEPTH
FROM DEPARTMENT D
Join t on d. PARENT_ID = T. ID
)
Select id, NAME, PARENT_ID, PATH, DEPTH FROM T
Order by path;

Id name PARENT_ID PATH DEPTH
1 DEPARTMENT_1 1
11 DEPARTMENT_11 1, 11 2
111 DEPARTMENT_111 111, 3
12 DEPARTMENT_12 1 1, 12 2
121 DEPARTMENT_121 12 121, 3
122 DEPARTMENT_122 12 122, 3

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: