Recursive SQL queries with PostgreSQL

PostgreSQL provides WITH statement (known as Common Table Expression, usually referred as CTE) which allows to build recursive queries. Its structure can be described with the following sudo code:

WITH RECURSIVE cte AS (
    SELECT * FROM table WHERE table.parent_id IS NULL -- basic query
    UNION ALL
    SELECT t.* FROM table t INNER JOIN cte c ON cte.id = t.parent_id -- recursive query
) SELECT * FROM cte;

Let’s imagine having a simple folders structure on some operating system:

CREATE TABLE folders (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT 
);

INSERT INTO folders(name, parent_id) VALUES
  ('home', NULL),
  ('root', NULL),
  ('user', 1),
  ('documents', 3),
  ('pictures', 3),
  ('downloads', 3),
  ('vacations', 5),
  ('baltic sea', 7),
  ('tatra mountains', 7);

We can print a list of folders and their parents using the following RECURSIVE query:

WITH RECURSIVE subfolders AS (
  SELECT id, name, NULL AS parent FROM folders WHERE parent_id IS NULL
  UNION ALL
  SELECT f.id, f.name, s.name FROM folders f
  INNER JOIN subfolders s ON f.parent_id = s.id
) SELECT * FROM subfolders;
id name parent
1 home  
2 root  
3 user home
4 documents user
5 pictures user
6 downloads user
7 vacations pictures
8 baltic sea vacations
9 tatra mountains vacations

In the first invocation, the query finds all root folders. In its second iteration, it uses these root folders as input values, to find their sub-folders. In the 3rd invocation, root folders’ directories are used to find user folders (documents, pictures, and downloads). It continues with iterations until there are no more directories with folders (baltic sea and tatra mountains vacations is the last one).

WITH statement can also be used to find a path of a specific folder:

WITH RECURSIVE childs AS (
    SELECT id, name, parent_id FROM folders WHERE name = 'baltic sea'
    UNION ALL
    SELECT f.id, f.name, f.parent_id FROM folders f
    INNER JOIN childs c ON f.id = c.parent_id
) SELECT id, name FROM childs;
id name
8 baltic sea
7 vacations
5 pictures
3 user
1 home

Full examples can be found on dbfiddle.