PostgreSQL中如何检测ltree路径内是否存在重复节点

核心思路 #

把ltree路径拆分为独立节点列表，对比总节点数和去重后的节点数，如果两者不一致则说明存在重复节点。

快速实现（单条检测） #

直接用原生函数即可完成检测，无需自定义函数：

-- 示例1：存在重复节点，返回true
SELECT nlevel('0.1.2.3.4.3.9'::ltree) <> array_length(array_distinct(string_to_array('0.1.2.3.4.3.9'::text, '.')), 1);

-- 示例2：无重复节点，返回false
SELECT nlevel('0.1.2.3.4.5.9'::ltree) <> array_length(array_distinct(string_to_array('0.1.2.3.4.5.9'::text, '.')), 1);

封装为自定义函数（方便复用） #

如果需要频繁调用，可以封装为函数：

CREATE OR REPLACE FUNCTION ltree_has_duplicate_nodes(p_path ltree)
RETURNS boolean AS $$
BEGIN
  RETURN nlevel(p_path) <> array_length(array_distinct(string_to_array(p_path::text, '.')), 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用示例：

SELECT ltree_has_duplicate_nodes('0.1.2.3.4.3.9'::ltree); -- 结果为t
SELECT ltree_has_duplicate_nodes('0.1.2.3.4.5.9'::ltree); -- 结果为f

低版本PostgreSQL兼容方案 #

如果使用的PostgreSQL版本低于10（无array_distinct函数），可以使用如下写法：

CREATE OR REPLACE FUNCTION ltree_has_duplicate_nodes(p_path ltree)
RETURNS boolean AS $$
DECLARE
  total_nodes integer;
  distinct_nodes integer;
BEGIN
  SELECT nlevel(p_path) INTO total_nodes;
  SELECT COUNT(DISTINCT node) INTO distinct_nodes
  FROM unnest(string_to_array(p_path::text, '.')) node;
  RETURN total_nodes <> distinct_nodes;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

内容的提问来源于stack exchange，提问作者Milad