PostgreSQL ltree查询祖先结果无序问题及排序需求咨询

问题原因 #

PostgreSQL 查询返回结果默认是无序的，仅使用 path @> '1.38.39.41.42.43.49' 筛选时，数据库不会自动按 ltree 的层级关系排序，所以返回结果杂乱。@> 操作符仅负责匹配所有是目标路径祖先（含自身）的节点，不保证输出顺序。

实现期望顺序的方法 #

要得到从子节点到根节点的排序（如 49→43→42→41→39→38→1），可以利用 ltree 的 nlevel() 函数获取路径的层级数，按层级倒序排序；或直接按路径长度倒序排序，二者效果一致。

示例SQL语句：

SELECT id 
FROM magic 
WHERE path @> '1.38.39.41.42.43.49'
ORDER BY nlevel(path) DESC;

或按路径长度排序：

SELECT id 
FROM magic 
WHERE path @> '1.38.39.41.42.43.49'
ORDER BY length(path) DESC;

执行结果：

49 
43
42
41
39
38
1

关于ltree路径体现顺序的说明 #

不需要修改 ltree 路径本身的结构来体现顺序。ltree 的设计逻辑就是从根节点到子节点的层级路径（如 1.38.39...49），查询时只需通过 ORDER BY 子句就能灵活控制返回顺序，这是符合数据库设计规范的最优方式。

内容的提问来源于stack exchange，提问作者Pascal de Sélys