PostgreSQL函数中多值字符串数组NOT IN失效问题求解

你第二个查询失效的核心问题是：NOT IN不会把带大括号的字符串解析成数组。

在第一个正常工作的查询里，你用了A.inuri = ANY ('{...}')——PostgreSQL会自动把这个带大括号的字符串识别为数组类型，然后检查A.inuri是否等于数组中的任意一个元素，所以多值时能正常匹配。

但第二个查询里的A.inuri NOT IN ('{...}')，PostgreSQL会把整个{...}的内容当作一个单一的字符串值。也就是说，它在检查A.inuri是否等于这个完整的长字符串，而不是检查是否等于其中的某一个路径。单值的时候刚好你的路径和这个字符串完全一致，所以能生效；多值时显然没有任何A.inuri等于这个拼接后的长字符串，所以自然没法排除那三个项。

有两种简单的修正方式，根据你的习惯选就行：

方式一：用!= ANY()替代NOT IN（推荐，和第一个场景语法统一） #

直接沿用第一个场景的数组语法，把=换成!=即可，PostgreSQL会正确解析数组：

SELECT * 
FROM TableA A 
INNER JOIN TableB B ON A.inuri = B.resource_uri 
INNER JOIN TableI I ON I.resource_id = B.resource_id 
WHERE B.resource_type LIKE '%%' 
  AND outuri = './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)' 
  AND ("isDeleted" = 'false') 
  AND A.inuri != ANY ( 
    '{./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6), ./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c), ./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(baf1d782-77f8-4372-9601-47a486f0700a)}' 
  )

如果觉得字符串数组可读性差，也可以用ARRAY构造器，更清晰：

AND A.inuri != ANY ( ARRAY[
    './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)',
    './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c)',
    './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(baf1d782-77f8-4372-9601-47a486f0700a)'
] )

方式二：修正NOT IN的参数格式 #

如果一定要用NOT IN，需要把数组拆成单独的字符串值，而不是包裹在大括号里的单个字符串：

AND A.inuri NOT IN (
    './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)',
    './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c)',
    './a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(baf1d782-77f8-4372-9601-47a486f0700a)'
)

优先推荐方式一，因为它和你第一个场景的语法保持一致，后续维护起来更统一；而且当你需要动态生成数组的时候，用ANY()的数组格式也更方便，不需要额外拆分字符串。

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