How do you prevent infinite loops and duplicates in a recursive CTE that walks a graph?

Problem Statement

Explanation

Add a termination condition that stops expansion when there are no new edges to follow or a maximum depth is reached. In hierarchical data, stop when the parent is NULL or repeats. Track visited nodes in the recursive member. Accumulate the path and exclude nodes already seen. This prevents cycles from re-entering the same vertex and emitting duplicates.

Code Solution

SolutionRead Only

WITH RECURSIVE walk AS (
  SELECT id, parent_id, ARRAY[id] AS path FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, path || n.id
  FROM nodes n
  JOIN walk w ON w.id = n.parent_id
  WHERE n.id <> ALL(w.path)
)
SELECT * FROM walk;

Practice Sets

This question appears in the following practice sets:

CTEs, Subqueries & Advanced Joins

Next Question

Master Interviews
Anywhere, Anytime

How do you prevent infinite loops and duplicates in a recursive CTE that walks a graph?

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews Anywhere, Anytime

How do you prevent infinite loops and duplicates in a recursive CTE that walks a graph?

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews
Anywhere, Anytime