PostgreSQL中ANY()子句遇空数组时无数据返回问题咨询

原因 #

当@platformIds是空数组时，agency_platform = any(@platformIds)这个条件会直接返回false。PostgreSQL中，ANY操作符作用于空数组时，没有任何元素能匹配上，所以整个WHERE子句的逻辑与（AND）结果为false，导致没有行被筛选出来，自然查不到数据。而移除ANY子句后，只剩下agency_id=100的条件，只要符合该条件的行存在就能返回结果。

解决方法 #

可以通过判断数组是否为空，动态决定是否应用agency_platform的过滤条件，以下是几种可行的写法：

方法1：使用OR结合数组非空判断 #

SELECT agencyId, SUM(open)
FROM agencies 
WHERE agency_id = 100 
  AND (cardinality(@platformIds) = 0 OR agency_platform = ANY(@platformIds))
GROUP BY agencyId;

这里cardinality(@platformIds)用于获取数组长度，当数组为空（长度为0）时，直接跳过平台过滤条件；数组非空时才执行匹配。

方法2：用COALESCE替换空数组为全量平台值数组（已知平台取值范围时） #

如果agency_platform的取值是固定集合（比如只有'web'、'app'、'h5'），可以这样写：

SELECT agencyId, SUM(open)
FROM agencies 
WHERE agency_id = 100 
  AND agency_platform = ANY(COALESCE(@platformIds, ARRAY['web','app','h5']))
GROUP BY agencyId;

当@platformIds为空时，COALESCE会返回后面的全量平台数组，从而匹配所有符合agency_id=100的行。

方法3：使用CASE表达式 #

SELECT agencyId, SUM(open)
FROM agencies 
WHERE agency_id = 100 
  AND CASE WHEN cardinality(@platformIds) > 0 THEN agency_platform = ANY(@platformIds) ELSE TRUE END
GROUP BY agencyId;

逻辑和方法1一致，通过CASE判断数组是否为空，为空时返回TRUE（即不做平台过滤），否则执行平台匹配。

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