Skip to content

Instantly share code, notes, and snippets.

@pstef

pstef/recursive CTE tree

Last active May 28, 2017 19:36
Show Gist options
  • Save pstef/2bbbda08a900922872f004da335c1936 to your computer and use it in GitHub Desktop.
Save pstef/2bbbda08a900922872f004da335c1936 to your computer and use it in GitHub Desktop.
Download ZIP
Representing an adjacency list as a tree, using a recursive CTE
Raw
recursive CTE tree
WITH RECURSIVE
categories_rel (id, parent_id) AS (VALUES
(1, NULL), (2, NULL),
(11, 1), (12, 1), (23, 2),
(119, 11), (121, 12),
(1193, 119), (1193, 1)
),
tree (id, parent_id, r, path) AS (
SELECT id, parent_id, 0, ARRAY[id]::int[]
FROM categories_rel
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, r + 1, tree.path || c.id
FROM tree
JOIN categories_rel c ON c.parent_id = tree.id
)
SELECT repeat(' ', tree.r) || tree.id, tree.*
FROM tree
ORDER BY tree.path;
┌──────────┬──────┬───────────┬───┬─────────────────┐
│ ?column? │ id │ parent_id │ r │ path │
├──────────┼──────┼───────────┼───┼─────────────────┤
│ 1 │ 1 │ │ 0 │ {1} │
│ 11 │ 11 │ 1 │ 1 │ {1,11} │
│ 119 │ 119 │ 11 │ 2 │ {1,11,119} │
│ 1193 │ 1193 │ 119 │ 3 │ {1,11,119,1193} │
│ 12 │ 12 │ 1 │ 1 │ {1,12} │
│ 121 │ 121 │ 12 │ 2 │ {1,12,121} │
│ 1193 │ 1193 │ 1 │ 1 │ {1,1193} │
│ 2 │ 2 │ │ 0 │ {2} │
│ 23 │ 23 │ 2 │ 1 │ {2,23} │
└──────────┴──────┴───────────┴───┴─────────────────┘
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment