PostgreSQL查询报错：获取当前日期在指定区间的pricing表ID

报错原因 #

原SQL语句的WHERE子句存在语法错误：

• BETWEEN的正确用法是指定两个值作为范围边界，格式为[值] BETWEEN [左边界] AND [右边界]
• 错误地将布尔表达式（start_date >= '2022-10-19'、end_date <= '2022-10-20'）作为BETWEEN的边界，导致PostgreSQL尝试将date类型的current_date与boolean类型值比较，触发类型不匹配错误：

operator does not exist: date >= boolean LINE 3: where current_Date between start_date>=date '2022-10-19'...

修正后的SQL语句 #

根据需求提供两种正确写法：

场景1：查询满足「当前日期在记录的start_date与end_date之间」且「记录的start_date不早于2022-10-19、end_date不晚于2022-10-20」的id

SELECT id
FROM pricing
WHERE current_date BETWEEN start_date AND end_date
  AND start_date >= DATE '2022-10-19'
  AND end_date <= DATE '2022-10-20';

场景2：仅当当前日期处于2022-10-19至2022-10-20之间时，查询pricing表的所有id

SELECT id
FROM pricing
WHERE current_date BETWEEN DATE '2022-10-19' AND DATE '2022-10-20';

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