Recursive With Query in Postgres

Recursive WITH Queries in Postgres

Sometimes we might have a requirement for certain hierarchy relation in data.
This can be done with the help of Recursive WITH Queries(Recursive CTE) in Postgres.

Consider the example –
Location is stored in table location_hierarchy.Query output will have all the children of ‘India’ along with its depth relative to “India”. The result should look somewhat similar to this:

Sample Data:

In such cases, Self JOIN query will fail since the depth here is arbitrary and may change in future.

For this, we use a special query called WITH RECURSIVE. It is also called as PostgreSQL hierarchical query since it is usually used to query on hierarchical data, like the one which we are discussing. The WITH RECURSIVE actually is an extension of WITH query which is referred to as Common Table Expressions(CTE) in PostgreSQL. WITH query can be seen as forming a temporary table(s) which has a scope for a single query or as a named sub-query.

Query :
WITH RECURSIVE children AS (
SELECT child, 1 AS depth ---|Non
FROM locatio_hierarchy --|Recursive
WHERE parent = 'India' ---|Part

UNION ALL

SELECT a.child, depth+1 ---|Recursive
FROM locatio_hierarchy a --|Part
JOIN children b ON(a.parent = b.child) ---|
)
SELECT * FROM children

Output:

Regards
Prasad Kshirsagar

Free Open Source BI Platform

• AI-Powered Analytics
• Embedded Analytics
• Single Sign-On (SSO)
• Multi-Tenancy
• Paginated Canned Reporting
• Interactive Dashboard
• White Labeling

Download Free