Recursive CTE | PostgreSQL Tutorial

Recursive CTE in Common Table Expression (CTE) helps you write SQL that is easier to test, review, and operate at scale.

Introduction to Recursive CTE

Use Recursive CTE to break complex SQL into readable, reusable steps.

Commonly paired with: SELECT, FROM, WHERE, JOIN.

Practical examples with Recursive CTE in PostgreSQL

Reference pattern: start from canonical syntax and keep it explicit.

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  JOIN category_tree t ON t.id = c.parent_id
)
SELECT * FROM category_tree;

Production-style scenario: apply the same concept to realistic application data.

SELECT
  o.order_id,
  o.total_amount,
  o.placed_at
FROM orders o
WHERE o.placed_at >= now() - interval '30 days'
ORDER BY o.placed_at DESC
LIMIT 50;

Additional example: use a variation to validate behavior and edge cases.

SELECT current_database(), current_user, now();

Production tips

Prefer explicit column lists and deterministic ordering when results feed APIs or batch jobs.
Validate plans with EXPLAIN before adding indexes, then re-check after schema changes.
Keep DDL, data backfills, and cleanups in transactions when possible to avoid partial state.
Use isolated environments for risky changes so query tuning and schema experiments stay safe.

Vela workflow tip

Test this pattern in an isolated branch database, share the result with your team, and promote only after query plans and row counts look correct.

Reference: PostgreSQL official documentation.