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.
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.
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.
-- 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 ...
)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.
-- 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
...
)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.
-- 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
)Break down complex recursive CTEs into simpler parts. If you have multiple CTEs or complex joins, PostgreSQL might struggle to parse the recursion.
-- 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;If using multiple CTEs, ensure there are no circular references. CTEs can reference earlier CTEs, but not later ones or themselves in invalid ways.
-- 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
)Create a minimal working example to isolate the issue. Start with a simple recursive CTE and gradually add complexity.
-- 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 outputThe "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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL