Mastering PostgreSQL CTEs: Recursive Queries for Hierarchical Data

Mastering PostgreSQL CTEs: Recursive Queries for Hierarchical Data Common Table Expressions (CTEs) are a powerful feature in PostgreSQL that allow you to write modular, readable queries. When used recursively, CTEs unlock the ability to traverse hierarchical structures such as trees, organizational charts, or folder systems directly within SQL. In this article, we’ll explore recursive CTEs with real-world use cases. 1. What Is a Recursive CTE? A recursive CTE is a CTE that references itself. It consists of two parts: the anchor member (the base case) and the recursive member (which references the CTE itself). 2. Sample Hierarchical Table CREATE TABLE categories ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, parent_id INT REFERENCES categories(id) ); Here’s some sample data: INSERT INTO categories (name, parent_id) VALUES ('Electronics', NULL), ('Laptops', 1), ('Smartphones', 1), ('Gaming Laptops', 2), ('Ultrabooks', 2), ('Android', 3), ('iOS', 3); 3. Writing a Recursive CTE Let’s find the entire hierarchy under "Electronics": WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, 1 AS depth FROM categories WHERE name = 'Electronics' UNION ALL SELECT c.id, c.name, c.parent_id, ct.depth + 1 FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree; This returns a tree of categories beneath "Electronics", complete with their depth levels. 4. Ordering the Tree Add an array to keep track of path order: WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, ARRAY[id] AS path FROM categories WHERE name = 'Electronics' UNION ALL SELECT c.id, c.name, c.parent_id, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree ORDER BY path; 5. Use Case: Breadcrumb Navigation To display breadcrumbs from a leaf node up to the root: WITH RECURSIVE breadcrumbs AS ( SELECT id, name, parent_id FROM categories WHERE name = 'Ultrabooks' UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN breadcrumbs b ON c.id = b.parent_id ) SELECT * FROM breadcrumbs ORDER BY id; 6. Performance Tips Use indexes on id and parent_id. Limit depth if the tree is very deep to prevent runaway recursion. Conclusion Recursive CTEs let you work with complex hierarchical data structures directly in PostgreSQL, without the need for additional application logic. Whether you're building category trees, permission hierarchies, or threaded comments, mastering recursive queries is an essential skill for serious SQL developers. If this post helped you, consider supporting me: buymeacoffee.com/hexshift

Apr 15, 2025 - 23:54

Mastering PostgreSQL CTEs: Recursive Queries for Hierarchical Data

Common Table Expressions (CTEs) are a powerful feature in PostgreSQL that allow you to write modular, readable queries. When used recursively, CTEs unlock the ability to traverse hierarchical structures such as trees, organizational charts, or folder systems directly within SQL. In this article, we’ll explore recursive CTEs with real-world use cases.

1. What Is a Recursive CTE?

A recursive CTE is a CTE that references itself. It consists of two parts: the anchor member (the base case) and the recursive member (which references the CTE itself).

2. Sample Hierarchical Table

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT REFERENCES categories(id)
);

Here’s some sample data:

INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Laptops', 1),
('Smartphones', 1),
('Gaming Laptops', 2),
('Ultrabooks', 2),
('Android', 3),
('iOS', 3);

3. Writing a Recursive CTE

Let’s find the entire hierarchy under "Electronics":

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories
  WHERE name = 'Electronics'
  
  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

This returns a tree of categories beneath "Electronics", complete with their depth levels.

4. Ordering the Tree

Add an array to keep track of path order:

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, ARRAY[id] AS path
  FROM categories
  WHERE name = 'Electronics'

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.path || c.id
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

5. Use Case: Breadcrumb Navigation

To display breadcrumbs from a leaf node up to the root:

WITH RECURSIVE breadcrumbs AS (
  SELECT id, name, parent_id
  FROM categories
  WHERE name = 'Ultrabooks'

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM categories c
  JOIN breadcrumbs b ON c.id = b.parent_id
)
SELECT * FROM breadcrumbs ORDER BY id;

6. Performance Tips

Use indexes on id and parent_id.
Limit depth if the tree is very deep to prevent runaway recursion.

Conclusion

Recursive CTEs let you work with complex hierarchical data structures directly in PostgreSQL, without the need for additional application logic. Whether you're building category trees, permission hierarchies, or threaded comments, mastering recursive queries is an essential skill for serious SQL developers.

If this post helped you, consider supporting me: buymeacoffee.com/hexshift