PostgreSQL查询array列包含指定范围日期的替代方法

PostgreSQL 中ANY(数组)的语法限定了它只能出现在比较运算符的右侧，所以你尝试的ANY(dates) BETWEEN ...写法不符合语法规范，无法正常运行。

方案1：EXISTS + unnest（可读性最高） #

这个写法完全符合常规的比较书写习惯，逻辑直观，全版本兼容：

SELECT *
FROM archive
WHERE EXISTS (
    SELECT 1
    FROM unnest(dates) AS d(date_val)
    WHERE date_val BETWEEN '2021-07-01' AND '2021-09-30'
);

逻辑说明：将dates数组拆分为单行的日期值，检查是否存在任意一个值落在指定的日期区间内。

方案2：范围包含 + ANY（写法最简洁） #

PostgreSQL 支持范围类型，使用闭区间日期范围的包含运算符配合ANY，写法精简，易读性也很高：

SELECT *
FROM archive
WHERE daterange('2021-07-01', '2021-09-30', '[]') @> ANY(dates);

逻辑说明：daterange第三个参数[]表示生成包含起止日期的闭区间，@>是包含运算符，整条语句意为「该日期区间包含dates数组中的任意一个元素」，完全匹配你的需求。

方案对比 #

• 方案1兼容性最好，适合团队协作场景，代码可读性最高，后期维护成本最低
• 方案2写法更精简，性能和原生ANY写法基本一致，适合追求代码简洁的场景
• 你最初使用的双ANY写法性能最优，只是写法不符合常规阅读习惯，如果团队接受也可以在生产环境使用

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