Recursive vs regular CTE

Question

Accepted Answer

The defining characteristic is self-reference: the recursive member's FROM clause names the CTE itself. A regular CTE is just a named subquery - it runs once and produces a fixed result. The RECURSIVE keyword in WITH RECURSIVE is required in PostgreSQL to enable self-reference; without it, the database treats the CTE as a regular named subquery and raises an error if you try to reference it within itself.