PostgreSQL如何对其他列值使用string_to_array函数查询

原写法问题 #

你写的SQL存在两个核心问题：

• string_to_array函数返回值是数组类型，IN操作符无法直接匹配数组，需要搭配数组匹配语法，或者先将数组拆分为多行集合
• 函数内嵌套多余的select子查询，语法不符合PostgreSQL规范，直接传入字段引用作为参数即可

正确实现方案 #

结合你的需求（查询id属于id=39租户ancestry字段按'/'拆分结果的所有记录），有两种常用写法：

方案1：使用ANY操作符匹配数组（写法最简洁） #

SELECT * FROM tenants 
WHERE id = ANY(
    -- 拆分后转整数数组，和id字段类型对齐
    SELECT string_to_array(ancestry, '/')::int[]
    FROM tenants 
    WHERE id = 39
);

方案2：使用unnest将数组拆分为行集合，适配IN语法 #

SELECT * FROM tenants 
WHERE id IN (
    -- unnest把数组拆成单列多行，供IN匹配
    SELECT unnest(string_to_array(ancestry, '/')::int[])
    FROM tenants 
    WHERE id = 39
);

效果验证 #

结合给出的示例数据：

• id=39的租户ancestry字段值为1/35
• string_to_array(ancestry, '/')拆分后得到文本数组{'1','35'}，转int数组后为{1,35}
• 最终查询会返回id=1、id=35的两条租户记录，符合预期

split_part函数的使用场景 #

如果你不需要拆分出所有值，只需要取分隔后指定位置的单个值，可以用split_part函数，语法为split_part(待拆分字段, 分隔符, 位置序号)，位置从1开始计数：

• 取id=39租户ancestry的第一层父id：split_part(ancestry, '/', 1) 返回'1'
• 取第二层父id：split_part(ancestry, '/', 2) 返回'35'

边界情况处理 #

如果ancestry字段存在空值或空字符串，拆分后会产生无效的空元素，可以用array_remove过滤掉转换后产生的null值，避免报错：

SELECT * FROM tenants 
WHERE id = ANY(
    SELECT array_remove(string_to_array(ancestry, '/')::int[], NULL)
    FROM tenants 
    WHERE id = 39
);

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