Representing an adjacency list as a tree, using a recursive CTE

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}          │
└──────────┴──────┴───────────┴───┴─────────────────┘