Recursive CTE for hierarchical data in PostgreSQL

Introduction

Recursive CTEs help you find and show data that is connected in a chain, like family trees or company departments.

When you want to list all employees under a manager in a company.

When you need to show folder and subfolder structures on a computer.

When you want to find all parts that make up a product in a manufacturing process.

When you want to trace a family tree from a person to their ancestors.

When you want to display categories and subcategories in an online store.

Syntax

PostgreSQL

WITH RECURSIVE cte_name (column_list) AS (
  -- Anchor member: select the starting rows
  SELECT columns
  FROM table
  WHERE condition
  UNION ALL
  -- Recursive member: select rows related to previous rows
  SELECT columns
  FROM table
  JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

The anchor member is the starting point of the recursion.

The recursive member repeats, adding related rows until no more are found.

Examples

This finds all employees under the top manager, including the manager.

PostgreSQL

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL  -- start from top manager
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

This lists all folders and their subfolders starting from root folders.

PostgreSQL

WITH RECURSIVE folder_tree AS (
  SELECT folder_id, parent_folder_id, folder_name
  FROM folders
  WHERE parent_folder_id IS NULL  -- root folders
  UNION ALL
  SELECT f.folder_id, f.parent_folder_id, f.folder_name
  FROM folders f
  JOIN folder_tree ft ON f.parent_folder_id = ft.folder_id
)
SELECT * FROM folder_tree;

Sample Program

This example creates a simple employee table with managers and uses a recursive CTE to find all employees under the top manager Alice.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  manager_id INT,
  name TEXT
);

INSERT INTO employees (manager_id, name) VALUES
  (NULL, 'Alice'),
  (1, 'Bob'),
  (1, 'Carol'),
  (2, 'David'),
  (2, 'Eve'),
  (3, 'Frank');

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY employee_id;

Important Notes

Recursive CTEs run until no new rows are added, so be careful to avoid infinite loops.

Time complexity depends on the depth and number of related rows; it can be costly for very deep or large hierarchies.

Use recursive CTEs when you need to explore hierarchical or tree-like data; for flat data, simple queries are better.

Summary

Recursive CTEs let you query hierarchical data step-by-step.

Start with a base case, then add related rows repeatedly.

Useful for trees like org charts, folders, or parts lists.