How to Run Hierarchical Queries with PostgreSQL?

In the area of database management, dealing with hierarchical data structures has unique challenges. Whether it’s organizational charts, category hierarchies, or file systems, efficiently querying and traversing hierarchical data is essential for many applications.

PostgreSQL, a powerful relational database management system, offers robust support for handling hierarchical queries through the use of recursive queries and Common Table Expressions (CTEs). In this article, We will learn about

How to run Hierarchical Queries with PostgreSQL?

Hierarchical queries involve retrieving data that is structured in a tree-like format, where each record has a relationship with other records in the same table. PostgreSQL provides the WITH RECURSIVE clause along with Common Table Expressions (CTEs) to handle hierarchical queries effectively. Below are the approaches to run Hierarchical Queries with PostgreSQL:

1. Using UNION ALL Operator
2. Using JOIN and LEVEL
3. Using Path Concatenation

Let’s set up an environment

To understand How to run Hierarchical Queries with PostgreSQL we need a table on which we will perform various operations and queries. Here we will consider a table called Organization which contains ID, ParentID, and Name as Columns.

-- Create the Organization table
CREATE TABLE Organization (
    ID SERIAL PRIMARY KEY,
    ParentID INTEGER,
    Name TEXT
);

-- Insert sample data
INSERT INTO Organization (ParentID, Name) VALUES 
    (NULL, 'CEO'),
    (1, 'Manager1'),
    (1, 'Manager2'),
    (2, 'Employee1'),
    (2, 'Employee2'),
    (3, 'Employee3'),
    (3, 'Employee4');

Out table Organization looks below:

table

1. Using UNION ALL Operator

This approach uses the UNION ALL operator to combine the results of the anchor and recursive members within the same CTE. It efficiently traverses the hierarchy.

Example:

WITH RECURSIVE HierarchyCTE AS (
    SELECT 
        ID,
        ParentID,
        Name,
        0 AS Level
    FROM 
        Organization
    WHERE 
        ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        t.ID,
        t.ParentID,
        t.Name,
        h.Level + 1
    FROM 
        Organization t
    JOIN 
        HierarchyCTE h ON t.ParentID = h.ID
)

SELECT 
    ID,
    ParentID,
    Name,
    Level
FROM 
    HierarchyCTE
ORDER BY 
    Level, ID;

Output:

Recursive Query using UNION ALL Operator

2. Using JOIN and LEVEL

This approach introduces a Level column to keep track of the depth in the hierarchy. It uses the WITH RECURSIVE clause to define the recursive operation.

Example:

WITH RECURSIVE HierarchyCTE AS (
    SELECT 
        ID,
        ParentID,
        Name,
        0 AS Level
    FROM 
        Organization
    WHERE 
        ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        t.ID,
        t.ParentID,
        t.Name,
        h.Level + 1
    FROM 
        Organization t
    JOIN 
        HierarchyCTE h ON t.ParentID = h.ID
)

SELECT 
    ID,
    ParentID,
    Name,
    Level
FROM 
    HierarchyCTE
ORDER BY 
    Level, ID;

Output:

Recursive Query using JOIN and LEVEL

3. Using Path Concatenation

This approach includes a column for path concatenation, where the path to each node is represented as a concatenated string. It can be useful when you need to track the path or lineage of each node in the hierarchy.

Example:

WITH RECURSIVE HierarchyCTE AS (
    SELECT 
        ID,
        ParentID,
        Name,
        CAST(ID AS TEXT) AS Path
    FROM 
        Organization
    WHERE 
        ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        t.ID,
        t.ParentID,
        t.Name,
        h.Path || '->' || t.ID
    FROM 
        Organization t
    JOIN 
        HierarchyCTE h ON t.ParentID = h.ID
)

SELECT 
    ID,
    ParentID,
    Name,
    Path
FROM 
    HierarchyCTE
ORDER BY 
    Path;

Output:

Recursive Query using Path Concatenation

Conclusion

Overall, PostgreSQL’s support for hierarchical queries through recursive queries and CTEs provides a powerful tool for querying and traversing hierarchical data structures. By understanding the WITH RECURSIVE clause and the UNION ALL operator, developers can efficiently navigate complex hierarchical data models. Additionally, the ability to use path concatenation and track the depth of the hierarchy adds versatility to PostgreSQL’s hierarchical query capabilities.