Recursive CTE - Path Enumeration

Recursive CTE - Path Enumeration is a SQL statement in the CTE (WITH) category. Recursive CTE that builds full path strings for hierarchical data like file systems or categories. The syntax is WITH RECURSIVE paths AS (SELECT id, name, CAST(name AS TEXT) AS path FROM nodes WHERE parent IS NULL UNION ALL SELECT ...). It returns full path hierarchy. A typical example: WITH RECURSIVE file_tree AS ( SELECT id, name, parent_id, name AS path, 'file' AS type FROM files WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, CONCAT(ft.path, '/', f.name), f.type FROM files f JOIN file_tree ft ON f.parent_id = ft.id ) SELECT id, path, type FROM file_tree ORDER BY path; -- Generates paths like: -- Documents -- Documents/Reports -- Documents/Reports/Q1_Report.pdf A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query.