Recursive postgres query using a common table expression

Postgres/SQL

Here is a short example on how to build a recursive postgresql query using a Common Table Expression (CTE). A CTE is a kind of subquery which can join itself to build recursive queries.

Our recursive query to fetch the pages and the category path:


with recursive category_hierarchy (id, title, path) as 
(
    select id, title, '' || title AS path from categories WHERE parent is null
    UNION ALL
    select c.id, c.title, category_hierarchy.path || ' > ' || c.title from categories c
    inner JOIN category_hierarchy ON c.parent = category_hierarchy.id
)
select p.*, hi.path from pages p
JOIN category_hierarchy hi ON p.parent = hi.id

The CTE starts with a query for the root categories (categories whose parent column is NULL). The result is UNIONed with the recursive JOINed result for the result of the categories.

And this is the result:


id  title                   parent  path
1   Awesome cat pictures    3       Blog > Animals > Cats
2   Some dog movies         4       Blog > Animals > Dogs
3   About security in IT    6       Blog > Computer > Security
4   New hardware            2       Blog > Computer
5   Welcome                 1       Blog