42P19ERRORTier 2 — Caution✅ HIGH confidence

invalid recursion

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

SQLSTATE 42P19 is a Postgres-specific error raised when a recursive CTE (WITH RECURSIVE) is structured in an invalid way — for example, the recursive term references the CTE in a context where recursion is not permitted.

Why it happens

1The recursive term of a WITH RECURSIVE CTE references the recursive CTE name in a subquery, aggregate, or other disallowed position
2Multiple recursive references in the recursive term

How to reproduce

Invalid recursion in WITH RECURSIVE.

trigger — this will ERROR

WITH RECURSIVE t(n) AS (
  SELECT 1
  UNION ALL
  SELECT COUNT(*) FROM t -- aggregate on recursive reference is invalid
)
SELECT * FROM t LIMIT 10;

ERROR: aggregate functions are not allowed in a recursive query's recursive term

Fix 1: Restructure the recursive CTE to follow valid recursion patterns

When writing recursive CTEs.

fix

WITH RECURSIVE t(n) AS (
  SELECT 1            -- base case
  UNION ALL
  SELECT n + 1 FROM t -- valid: simple reference to t
  WHERE n < 10
)
SELECT * FROM t;

Why this works

The recursive term can only reference the CTE name in a simple FROM clause, not inside aggregates, subqueries with outer references, or certain other constructs.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/queries-with.html

🔧 Source ref: Class 42 — Syntax Error or Access Rule Violation (Postgres-specific)

Confidence assessment

✅ HIGH confidence

Postgres-specific. Stable across versions.