Postgres Tip: Guard Against Infinite Recursion with Arrays

A lesser-known Postgres strength is its array column type, which offers a clean safeguard for recursive queries that might otherwise loop forever — for example, when a data-entry mistake creates a circular manager-employee relationship in an org chart.

The approach adds a calculated “visited” array column to your recursive CTE. The base query seeds it with the starting employee’s ID:

SELECT empno, ename,
       ename AS path,
       ARRAY[empno] as visited
FROM emp
WHERE empno = 7566

In the recursive clause, you append each newly encountered employee to the running array. The key filter is the array overlap operator (&&), which rejects any row whose ID is already present in the visited set:

UNION ALL
SELECT emp.empno, emp.ename,
       ctename.path || ' -> ' || emp.ename,
       ctename.visited || emp.empno
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
WHERE
   NOT ctename.visited && ARRAY[emp.empno]

Each iteration extends the array with every node traversed. Once a loop would come back to a previously visited location, the condition fails and the recursion stops safely.