如何在PostgreSQL中筛选ID属于ancestry字段拆分列表的关联数据？

我的departments表中，ancestry字段存储了物化路径（格式如id1/id2/id3，例如1/29/13）。我需要筛选出ID属于该字段拆分出的ID列表的记录，效果类似下面的SQL：

SELECT *
FROM departments
WHERE ID IN (1,29,427)

但IN子句中的(1,29,427)需要动态来自目标记录的ancestry字段。我尝试了几种写法都无法正常运行，请问该如何修改？

我尝试的SQL代码：

-- 查询ID为433的部门的所有直接祖先
SELECT ancestors.id, ancestors.ancestry, ancestors.name
FROM departments target, departments ancestors 
--WHERE ancestors.id IN (1,29)
--WHERE ancestors.id IN STRING_TO_ARRAY(target.ancestry, '/')::integer
--WHERE ancestors.id IN (unnest(STRING_TO_ARRAY(target.ancestry, '/')::INTEGER[]))
--WHERE ancestors.id IN (target.ancestry)
WHERE ancestors.id IN (STRING_TO_ARRAY(target.ancestry, '/')::INTEGER[])
AND target.id = 433

正确写法 #

在PostgreSQL中，IN子句不能直接接收数组类型参数，需要用数组匹配运算符或者展开数组的方式处理，以下是两种可行方案：

方案1：用= ANY()匹配数组元素 #

这是最简洁的实现方式，直接用数组包含运算符匹配ID：

SELECT ancestors.id, ancestors.ancestry, ancestors.name
FROM departments target
JOIN departments ancestors 
  ON ancestors.id = ANY(STRING_TO_ARRAY(target.ancestry, '/')::INTEGER[])
WHERE target.id = 433;

方案2：用unnest展开数组后关联 #

如果需要兼容旧版本PostgreSQL，或者要对拆分后的ID做额外处理，可以先把数组拆分成单行数据再关联：

SELECT ancestors.id, ancestors.ancestry, ancestors.name
FROM departments target
JOIN departments ancestors 
  ON ancestors.id IN (SELECT unnest(STRING_TO_ARRAY(target.ancestry, '/')::INTEGER[]))
WHERE target.id = 433;

原写法失效原因 #

• IN子句仅支持逗号分隔的常量列表，或者返回单列结果的子查询，无法直接识别数组类型。
• STRING_TO_ARRAY返回的是数组对象，必须用= ANY()这类数组专用运算符，或者通过unnest把数组转成行数据才能被IN识别。

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