PostgreSQL 9.6中如何将邻接列表转换为ltree类型？

嘿，这个需求用PostgreSQL的递归CTE就能轻松搞定，而且是最直接的方法！

首先，得先确保你的PostgreSQL实例已经安装了ltree扩展——毕竟这个类型不是默认自带的，跑下面这条语句启用它：

CREATE EXTENSION IF NOT EXISTS ltree;

接下来，核心逻辑用递归CTE来实现，这是PostgreSQL处理层级数据的原生方案，代码简洁易懂，完全贴合你的需求：

WITH RECURSIVE dept_path AS (
    -- 第一步：先找出所有根节点（parent_id=-1，排除那个id=-1的无效空节点）
    SELECT 
        id, 
        name, 
        CAST(id AS TEXT) AS path_text
    FROM departments
    WHERE parent_id = -1 AND id != -1
    
    UNION ALL
    
    -- 第二步：递归拼接子节点的路径
    SELECT 
        d.id, 
        d.name, 
        dp.path_text || '.' || CAST(d.id AS TEXT)
    FROM departments d
    INNER JOIN dept_path dp 
        ON d.parent_id = dp.id
)
-- 最后把文本路径转成ltree类型，输出结果
SELECT 
    id, 
    name, 
    path_text::ltree AS path
FROM dept_path
ORDER BY id;

代码解释： #

• 锚点部分：筛选出所有顶级部门（parent_id=-1且不是那个id=-1的无效节点），把它们的id转成文本作为初始路径。
• 递归部分：通过parent_id关联父节点的路径，把父节点的路径和当前节点的id用.拼接，形成子节点的完整路径文本。
• 最终转换：把拼接好的文本路径直接强制转换为ltree类型，就得到了你想要的格式。

执行这条SQL后，输出结果会完全匹配你给出的示例：

这个方法的优势在于：不需要自定义函数，完全用PostgreSQL原生语法实现，代码简洁易维护，对于中小规模的层级数据性能也足够出色。

内容的提问来源于stack exchange，提问作者Vladimir M.