Find Parent Recursively using Query

by

Here’s a complete example. First the DDL:

test=> CREATE TABLE node (
test(>   id SERIAL,
test(>   label TEXT NOT NULL, -- name of the node
test(>   parent_id INT,
test(>   PRIMARY KEY(id)
test(> );
NOTICE:  CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE

…and some data…

test=> INSERT INTO node (label, parent_id) VALUES ('n1',NULL),('n2',1),('n3',2),('n4',3);
INSERT 0 4
test=> INSERT INTO node (label) VALUES ('garbage1'),('garbage2'), ('garbage3');
INSERT 0 3
test=> INSERT INTO node (label,parent_id) VALUES ('garbage4',6);
INSERT 0 1
test=> SELECT * FROM node;
id |  label   | parent_id 
----+----------+-----------
 1 | n1       |          
 2 | n2       |         1
 3 | n3       |         2
 4 | n4       |         3
 5 | garbage1 |          
 6 | garbage2 |          
 7 | garbage3 |          
 8 | garbage4 |         6
(8 rows)

This performs a recursive query on every id in node:

test=> 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 ORDER BY n.id ASC;
id |  label   | parent_id | depth |    path    
----+----------+-----------+-------+------------
 1 | n1       |           |     1 | 1
 2 | n2       |         1 |     2 | 1->2
 3 | n3       |         2 |     3 | 1->2->3
 4 | n4       |         3 |     4 | 1->2->3->4
 5 | garbage1 |           |     1 | 5
 6 | garbage2 |           |     1 | 6
 7 | garbage3 |           |     1 | 7
 8 | garbage4 |         6 |     2 | 6->8
(8 rows)

This gets all of the descendents WHERE node.id = 1:

test=> 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.id = 1
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;
id | label | parent_id | depth |    path    
----+-------+-----------+-------+------------
 1 | n1    |           |     1 | 1
 2 | n2    |         1 |     2 | 1->2
 3 | n3    |         2 |     3 | 1->2->3
 4 | n4    |         3 |     4 | 1->2->3->4
(4 rows)

The following will get the path of the node with id 4:

test=> 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;
id | label | parent_id | depth |    path    
----+-------+-----------+-------+------------
 4 | n4    |         3 |     4 | 1->2->3->4
(1 row)

And let’s assume you want to limit your search to descendants with a depth less than three (note that depth hasn’t been incremented yet):

test=> 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.id = 1
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 AND p.depth < 2
)
SELECT * FROM nodes_cte AS n;
 id | label | parent_id | depth | path 
----+-------+-----------+-------+------
  1 | n1    |           |     1 | 1
  2 | n2    |         1 |     2 | 1->2
(2 rows)

I’d recommend using an ARRAY data type instead of a string for demonstrating the “path”, but the arrow is more illustrative of the parent<=>child relationship.

More Related Contents:

  1. How to reset postgres’ primary key sequence when it falls out of sync?
  2. PostgreSQL LIKE query performance variations
  3. How to import CSV file data into a PostgreSQL table
  4. The forgotten assignment operator “=” and the commonplace “:=”
  5. Equivalent to unpivot() in PostgreSQL
  6. Passing user id to PostgreSQL triggers
  7. Is it possible to make a recursive SQL query?
  8. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  9. I forgot the password I entered during postgres installation
  10. Do nullable columns occupy additional space in PostgreSQL?
  11. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  12. How do I do large non-blocking updates in PostgreSQL?
  13. How to configure PostgreSQL to accept all incoming connections
  14. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  15. Disable PostgreSQL foreign key checks for migrations
  16. Prevent recursive trigger in PostgreSQL
  17. Spark Dataframes UPSERT to Postgres Table
  18. PostgreSQL Error: Relation already exists
  19. Using row_to_json() with nested joins
  20. Optimize Postgres query on timestamp range
  21. Use variable set by psql meta-command inside of DO block
  22. pgAdmin III Why query results are shortened?
  23. How do I handle opening/closing Db connection in a Go app?
  24. How to add a conditional unique index on PostgreSQL
  25. PostgreSQL disable more output
  26. PostgreSQL array_agg order
  27. How do I get min, median and max from my query in postgresql?
  28. PostgreSQL jsonb, `?` and JDBC
  29. Syntax error at end of input in PostgreSQL
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment