What causes "42P19: invalid_recursion"?

Missing or incorrect reference to the CTE name in the recursive term Using the CTE name in the non-recursive term (initial SELECT) Attempting to reference multiple CTEs in a way that creates circular dependencies

Is the "42P19: invalid_recursion" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

How to fix 42P19: invalid_recursion in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 181 views

The PostgreSQL error "42P19: invalid_recursion" occurs when a recursive Common Table Expression (CTE) is improperly structured. This syntax error prevents recursive queries from executing and typically indicates missing or incorrect references to the CTE itself in the recursive term. Fixing it requires understanding PostgreSQL's rules for recursive query construction.

Quick fix

Fixes 42P19: invalid_recursion

-- 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 ...
)

Verify CTE name references in recursive term
Check non-recursive term doesn't reference CTE
Verify UNION/UNION ALL placement
Simplify complex recursive CTEs

postgresql — error

$-- 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 ...
)
42P19: invalid_recursion

What this error means

The "invalid_recursion" error (SQLSTATE 42P19) is a syntax error that occurs when PostgreSQL cannot properly parse a recursive Common Table Expression (CTE). Recursive CTEs allow queries to reference their own output, enabling operations like hierarchical data traversal, graph algorithms, and cumulative calculations. This error specifically indicates that the recursive part of the CTE violates PostgreSQL's syntax rules for recursion. Unlike runtime errors that occur during query execution, this is a parsing error that prevents the query from even starting. The database recognizes you're trying to write a recursive query but cannot interpret the recursion structure correctly. The error falls under class 42 (Syntax Error or Access Rule Violation), which means it's a problem with how the SQL is written rather than a data or permission issue. Proper recursive CTEs must follow strict structural requirements that PostgreSQL enforces at parse time.

Typical symptoms

Query fails immediately with error "42P19: invalid_recursion"
Recursive CTE syntax appears correct but PostgreSQL rejects it
Error occurs during query parsing, not during execution
Similar recursive queries work but this specific one fails
Query planner cannot generate an execution plan for the recursive CTE

Common causes

Missing or incorrect reference to the CTE name in the recursive term
Using the CTE name in the non-recursive term (initial SELECT)
Attempting to reference multiple CTEs in a way that creates circular dependencies
Incorrect UNION/UNION ALL placement between non-recursive and recursive terms
Syntax errors in the recursive term that make it unrecognizable as a recursive reference

How to fix "42P19: invalid_recursion"

1Verify CTE name references in recursive term

Check that your recursive term correctly references the CTE name. The recursive term must include a reference to the CTE itself in the FROM clause.

sql

-- 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 ...
)

2Check non-recursive term doesn't reference CTE

Ensure the non-recursive term (the first SELECT after WITH RECURSIVE) does NOT reference the CTE name. Only the recursive term should reference the CTE.

sql

-- 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
    ...
)

3Verify UNION/UNION ALL placement

Ensure UNION or UNION ALL appears between the non-recursive and recursive terms. The basic structure must be: non-recursive term, UNION/UNION ALL, recursive term.

sql

-- 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
)

4Simplify complex recursive CTEs

Break down complex recursive CTEs into simpler parts. If you have multiple CTEs or complex joins, PostgreSQL might struggle to parse the recursion.

sql

-- 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;

5Check for circular dependencies with multiple CTEs

If using multiple CTEs, ensure there are no circular references. CTEs can reference earlier CTEs, but not later ones or themselves in invalid ways.

sql

-- 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
)

6Test with minimal example

Create a minimal working example to isolate the issue. Start with a simple recursive CTE and gradually add complexity.

sql

-- 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

Advanced notes

The "invalid_recursion" error is a parser-level error, not an execution error. This means PostgreSQL's query parser cannot understand the recursive structure you've written.

Technical details:
1. PostgreSQL uses a bottom-up parser for SQL that must be able to build a parse tree for the entire query.
2. Recursive CTEs require special handling in the parser to recognize the self-reference.
3. The parser expects a very specific syntax pattern: WITH RECURSIVE name AS (non-recursive-term UNION [ALL] recursive-term).

Edge cases to consider:
1. Materialized vs Non-materialized CTEs: Using MATERIALIZED or NOT MATERIALIZED hints doesn't affect parsing, only execution.
2. CTE in subqueries: Recursive CTEs can be used in subqueries, but the same parsing rules apply.
3. View definitions: If this error occurs in a view definition, check that the view doesn't have syntax errors in its recursive CTE.
4. PostgreSQL version differences: Recursive CTE syntax has been stable since PostgreSQL 8.4, but very old versions might have different requirements.

Debugging tips:
1. Use psql with \set VERBOSITY verbose to get more detailed error messages.
2. Check PostgreSQL logs for additional parsing context.
3. Consider if you're using any PostgreSQL extensions that might affect SQL parsing.

Alternative approaches:
If you cannot fix the recursive CTE syntax, consider:
1. Using iterative application logic instead of database recursion.
2. Using a temporary table with a WHILE loop in a PL/pgSQL function.
3. For hierarchical data, consider using the ltree extension or nested set model.

Frequently asked questions

What does "42P19: invalid_recursion" mean?: The "invalid_recursion" error (SQLSTATE 42P19) is a syntax error that occurs when PostgreSQL cannot properly parse a recursive Common Table Expression (CTE). Recursive CTEs allow queries to reference their own output, enabling operations like hierarchical data traversal, graph algorithms, and cumulative calculations. This error specifically indicates that the recursive part of the CTE violates PostgreSQL's syntax rules for recursion.
How do I fix "42P19: invalid_recursion"?: The PostgreSQL error "42P19: invalid_recursion" occurs when a recursive Common Table Expression (CTE) is improperly structured. This syntax error prevents recursive queries from executing and typically indicates missing or incorrect references to the CTE itself in the recursive term. Fixing it requires understanding PostgreSQL's rules for recursive query construction.
What causes "42P19: invalid_recursion"?: Missing or incorrect reference to the CTE name in the recursive term Using the CTE name in the non-recursive term (initial SELECT) Attempting to reference multiple CTEs in a way that creates circular dependencies
Is the "42P19: invalid_recursion" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading

📚PostgreSQL Official Documentation
📖
postgresql.org - errcodes appendix(Official Docs)
📖
postgresql.org - queries with(Official Docs)
📖
postgresql.org - sql select(Official Docs)
📖
postgresql.org - sql createtable(Official Docs)