Return to Answer

Cycle detection is a built-in feature of recursive CTEs: _{demo at db<>fiddle}

1. CYCLE clauses informs it which value indicates a loop if it repeats
2. It's followed by a boolean field that it'll populate for the records with loops and an array field that holds the whole path/group with the cycle.

WITH recursive tmp AS ( SELECT supervisor_id, emp_id FROM incoming_records UNION ALL SELECT tmp.supervisor_id, t.emp_id FROM tmp INNER JOIN incoming_records AS t ON t.supervisor_id = tmp.emp_id )CYCLE emp_id SET is_cycle USING path ----- here SELECT * FROM tmp WHERE is_cycle; 

If you want to avoid them rather than select only those, switch to WHERE NOT is_cycle;.

The SQL-standard CYCLE clause for built-in cycle detection was added in v14.

Cycle detection is a built-in feature of recursive CTEs: _{demo at db<>fiddle}

1. CYCLE clause informs it which value indicates a loop if it repeats
2. It's followed by a boolean field, populated for the records with loops, then an array field that holds the whole path/group with the cycle.
3. Since the third field path accumulates all keys, it can grow quickly on larger and more complex sets. As an optimised, fast and memory-efficient alternative, consider pgrouting.

WITH recursive tmp AS ( SELECT supervisor_id, emp_id FROM incoming_records UNION ALL SELECT tmp.supervisor_id, t.emp_id FROM tmp INNER JOIN incoming_records AS t ON t.supervisor_id = tmp.emp_id )CYCLE emp_id SET is_cycle USING path ----- here SELECT * FROM tmp WHERE is_cycle; 

If you want to avoid them rather than select only those, switch to WHERE NOT is_cycle;.

The SQL-standard CYCLE clause for built-in cycle detection was added in v14.

Cycle detection is a built-in feature of recursive CTEs: _{demo at db<>fiddle}

1. CYCLE clauses informs it which value indicates a loop if it repeats
2. It's followed by a boolean field that it'll populate for the records with loops an array field that holds the whole path/group with the cycle.

WITH recursive tmp AS ( SELECT supervisor_id, emp_id FROM incoming_records UNION ALL SELECT tmp.supervisor_id, t.emp_id FROM tmp INNER JOIN incoming_records AS t ON t.supervisor_id = tmp.emp_id )CYCLE emp_id SET is_cycle USING path ----- here SELECT * FROM tmp WHERE is_cycle; 

If you want to avoid them rather than select only those, switch to WHERE NOT is_cycle;.

The SQL-standard CYCLE clause for built-in cycle detection was added in v14.

Cycle detection is a built-in feature of recursive CTEs: _{demo at db<>fiddle}

1. CYCLE clauses informs it which value indicates a loop if it repeats
2. It's followed by a boolean field that it'll populate for the records with loops and an array field that holds the whole path/group with the cycle.

WITH recursive tmp AS ( SELECT supervisor_id, emp_id FROM incoming_records UNION ALL SELECT tmp.supervisor_id, t.emp_id FROM tmp INNER JOIN incoming_records AS t ON t.supervisor_id = tmp.emp_id )CYCLE emp_id SET is_cycle USING path ----- here SELECT * FROM tmp WHERE is_cycle; 

If you want to avoid them rather than select only those, switch to WHERE NOT is_cycle;.

The SQL-standard CYCLE clause for built-in cycle detection was added in v14.

Cycle detection is a built-in feature of recursive CTEs: _{demo at db<>fiddle}

1. CYCLE clauses informs it which value indicates a loop if it repeats
2. It's followed by a boolean field that it'll populate for the records with loops an array field that holds the whole path/group with the cycle.

WITH recursive tmp AS ( SELECT supervisor_id, emp_id FROM incoming_records UNION ALL SELECT tmp.supervisor_id, t.emp_id FROM tmp INNER JOIN incoming_records AS t ON t.supervisor_id = tmp.emp_id )CYCLE emp_id SET is_cycle USING path ----- here SELECT * FROM tmp WHERE is_cycle; 

Cycle detection is a built-in feature of recursive CTEs: _{demo at db<>fiddle}

1. CYCLE clauses informs it which value indicates a loop if it repeats
2. It's followed by a boolean field that it'll populate for the records with loops an array field that holds the whole path/group with the cycle.

WITH recursive tmp AS ( SELECT supervisor_id, emp_id FROM incoming_records UNION ALL SELECT tmp.supervisor_id, t.emp_id FROM tmp INNER JOIN incoming_records AS t ON t.supervisor_id = tmp.emp_id )CYCLE emp_id SET is_cycle USING path ----- here SELECT * FROM tmp WHERE is_cycle; 

If you want to avoid them rather than select only those, switch to WHERE NOT is_cycle;.

The SQL-standard CYCLE clause for built-in cycle detection was added in v14.