📎 Webclip
Recursive SQL Queries with PostgreSQL
Recursive CTEs are presented as a way to handle self-referential data and tree-like or graph-like structures in SQL. The article first shows the formal WITH RECURSIVE pattern, then uses employee hierarchies and graph edges to demonstrate how recursion can build paths, levels, and repeated expansions.
Reading notes#
- Recursive queries are useful for self-referential data and tree-like data structures, such as manager-subordinate relationships, category hierarchies, flight maps, taxonomies, article links, and comment threads.
- Recursive queries use a CTE, or common table expression, introduced with the SQL
WITHclause to make complex queries more readable. - A recursive SQL query has a non-recursive part and a recursive part joined with
UNIONorUNION ALL. - In the simple numeric example, the base case selects
1, and the recursive part adds1until theWHEREclause stops the recursion. - An employee table with a self-referencing
manager_idcolumn is used to model a manager-subordinate hierarchy. - A recursive query can return the subtree under a given employee, using a
levelcolumn to show depth in the hierarchy. - The same hierarchy can be used to compute degrees of separation between two employees.
- A recursive query can also work from the bottom up, producing a job progression path such as
Developer > Manager > VP > CEO. - For graphs, the article uses an
edgestable withsrcanddestcolumns. - PostgreSQL arrays are used to store paths, concatenate visited nodes, and avoid cycles by checking that a new destination is not already in the path.
- Recursive queries can generate infinite streams of rows, and
LIMITcan safely return only the first rows from such a query. - PostgreSQL can also create recursive views, which the article describes as syntax sugar for recursive queries.
