How to fix 42P19: invalid_recursion in PostgreSQL

-- Correct: CTE references itself
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL  -- non-recursive term
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id  -- recursive term references CTE
)
SELECT * FROM employee_hierarchy;

-- Incorrect: Missing CTE reference
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT id, name, manager_id  -- No FROM clause referencing the CTE!
    FROM employees  -- This doesn't reference employee_hierarchy
    WHERE ...
)

-- Correct: Non-recursive term uses base tables only
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories  -- Base table, not CTE
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id  -- Recursive term references CTE
)

-- Incorrect: Non-recursive term references CTE
WITH RECURSIVE category_tree AS (
    SELECT * FROM category_tree  -- ERROR: Can't reference CTE here!
    WHERE parent_id IS NULL
    UNION ALL
    ...
)

-- Correct structure
WITH RECURSIVE numbers AS (
    VALUES (1)           -- Non-recursive term
    UNION ALL            -- Must be UNION or UNION ALL
    SELECT n + 1         -- Recursive term
    FROM numbers
    WHERE n < 100
)
SELECT * FROM numbers;

-- Incorrect: Missing UNION
WITH RECURSIVE numbers AS (
    VALUES (1)
    SELECT n + 1         -- ERROR: No UNION between terms
    FROM numbers
    WHERE n < 100
)

-- Complex CTE that might cause issues
WITH RECURSIVE complex_cte AS (
    SELECT a.id, b.name, c.value
    FROM table_a a
    JOIN table_b b ON a.b_id = b.id
    JOIN table_c c ON b.c_id = c.id
    WHERE a.parent_id IS NULL
    UNION ALL
    SELECT a2.id, b2.name, c2.value
    FROM table_a a2
    JOIN table_b b2 ON a2.b_id = b2.id
    JOIN table_c c2 ON b2.c_id = c2.id
    JOIN complex_cte ct ON a2.parent_id = ct.id  -- Complex join in recursive term
)

-- Try simplifying:
WITH RECURSIVE simple_cte AS (
    SELECT id, parent_id, name
    FROM table_a
    WHERE parent_id IS NULL
    UNION ALL
    SELECT a.id, a.parent_id, a.name
    FROM table_a a
    JOIN simple_cte s ON a.parent_id = s.id
)
SELECT s.*, b.name, c.value
FROM simple_cte s
JOIN table_b b ON s.b_id = b.id
JOIN table_c c ON b.c_id = c.id;

-- Valid: CTE2 references CTE1
WITH RECURSIVE cte1 AS (
    SELECT id FROM table1 WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id FROM table1 t JOIN cte1 c ON t.parent_id = c.id
),
cte2 AS (
    SELECT * FROM cte1  -- References earlier CTE
)
SELECT * FROM cte2;

-- Invalid: Circular reference
WITH RECURSIVE cte1 AS (
    SELECT * FROM cte2  -- ERROR: References later CTE
    UNION ALL
    ...
),
cte2 AS (
    SELECT * FROM cte1  -- ERROR: Circular reference
)

-- Minimal working example
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;

-- If this works, compare with your failing query
-- Look for differences in:
-- 1. CTE name references
-- 2. UNION/UNION ALL usage
-- 3. JOIN conditions in recursive term
-- 4. Column names and aliases

-- Use EXPLAIN to see query plan
EXPLAIN WITH RECURSIVE your_cte AS (...)
SELECT * FROM your_cte;
-- Look for parsing errors in the EXPLAIN output