invalid recursion

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;

expected output

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

Fix

Restructure the recursive CTE to follow valid recursion patterns

WHEN When writing recursive CTEs.

Restructure the recursive CTE to follow valid recursion patterns

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

Class 42 — Syntax Error or Access Rule Violation (Postgres-specific)

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

At a glance

PlatformPostgreSQL SQLSTATE Errors

Code42P19

ClassSyntax Error or Access Rule Violation

SeverityERROR

TierNotable

ConfidenceHIGH