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:
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