PostgreSQL 12兼容：数组匹配时返回指定值否则返回全部的WHERE子句

给定如下表表达式，需要实现当传入的数组参数包含4时，仅返回y.id=4的行；当数组不包含4时，返回所有行，要求兼容PostgreSQL 12版本。

原伪代码SQL：

SELECT y.id
  FROM (VALUES (4)
             , (1)
             , (2)
             , (6)) y (id)
WHERE y.id = < if ANY('{4}') then 4 else y.id >
;

期望结果：

• 数组包含4时的返回结果：

id
--
4

• 数组不包含4时的返回结果：

id
--
4
1
2
6

解决方案 #

以下两种写法均满足需求且兼容PostgreSQL 12：

写法1：使用CASE表达式 #

SELECT y.id
FROM (VALUES (4), (1), (2), (6)) y (id)
WHERE y.id = CASE WHEN 4 = ANY('{4}'::int[]) THEN 4 ELSE y.id END;

逻辑说明：

• 当数组包含4时，CASE表达式返回4，WHERE条件等价于y.id = 4，仅匹配id为4的行；
• 当数组不包含4时，CASE表达式返回y.id本身，WHERE条件等价于y.id = y.id，所有行都满足条件。

写法2：使用逻辑分支组合 #

SELECT y.id
FROM (VALUES (4), (1), (2), (6)) y (id)
WHERE (4 = ANY('{4}'::int[]) AND y.id = 4) 
   OR NOT (4 = ANY('{4}'::int[]));

逻辑说明：

• 拆分两个逻辑分支：数组包含4时，触发第一个条件（仅取id=4的行）；数组不包含4时，触发第二个条件（取所有行）。

注意：数组字面量需要显式转换为对应数据类型（如示例中的::int[]），否则PostgreSQL无法正确识别数组类型，导致ANY操作符失效。

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