Find parent row recursively in PostgreSQL using Query

In this blog, I am going to explain you about how to find parent hierarchy recursively of a row in PostgreSQL using query. I am using PostgreSQL database server.

Whenever we have a parent-child relationship in table rows then sometimes we need to check all the parents or maybe the last parent of any record. I want to write a sql query that will give the final parent of the entry. So if we have a long hierarchy of the rows then it might be difficult to get the last parent. So in PostgreSQL, I used the following SQL script to find the hierarchy of any row. The following will get the path of the node with ID 4:

WITH RECURSIVE nodes_cte(id, label, parent_id, depth, path) AS (
 SELECT tn.id, tn.label, tn.parent_id, 1::INT AS depth, tn.id::TEXT AS path 
 FROM node AS tn 
 WHERE tn.parent_id IS NULL
UNION ALL
 SELECT c.id, c.label, c.parent_id, p.depth + 1 AS depth, 
        (p.path || '->' || c.id::TEXT) 
 FROM nodes_cte AS p, node AS c 
 WHERE c.parent_id = p.id
)
SELECT * FROM nodes_cte AS n WHERE n.id = 4;

In the above PostgreSQL query we are using WITH RECURSIVE (Common Table Expressions). The above query will give the id, label and the path of the given id (in my case it is 4). Hope above query will save your time to traverse the parent id of a row in a table.