normalize_parent_path.sql

WITH RECURSIVE tree AS (
   SELECT id, parent_path, ARRAY[]::INTEGER[] AS ancestors
   FROM core.items
   WHERE parent_id is null

   UNION ALL

   SELECT i.id, i.parent_path, tree.ancestors || i.parent_id
   FROM core.items i, tree
   WHERE i.parent_id = tree.id
)
update core.items i
  set parent_path = array_to_string(array_prepend('root', ancestors::text[]), '.')::ltree
 from tree
 where tree.id = i.id;