PostgreSQL报错：operator does not exist: interval <= date 求解

嘿，这个报错我之前也踩过坑！咱们先把问题拆明白：

你写的CASE判断里，两边的数据类型完全不匹配，PostgreSQL自然会抛出错误：

• 左边的(CURRENT_DATE - MAX(date))计算后得到的是interval类型（比如'85 days'这种时间间隔格式）
• 右边的CURRENT_DATE - INTERVAL '90 days'得到的是date类型（比如'2024-05-02'这种具体日期）

这俩类型根本没法直接用<=做比较，所以才会出现ERROR: operator does not exist: interval <= date的提示。

两种靠谱的修正方案 #

方案1：直接比较下单日期（更直观高效）

既然我们要判断的是「过去90天内有下单」，不如直接检查最近一次下单日期是否落在90天窗口内——这样两边都是date类型，完全不会有类型冲突：

SELECT DISTINCT ON (accountid) 
    accountid, 
    customer, 
    round(AVG(qty),2) AS average_order_qty, 
    ROUND(AVG(total),2) AS avg_order_rev, 
    (CURRENT_DATE - MAX(date)) as days_since_order,
    CASE 
        WHEN MAX(date) >= CURRENT_DATE - INTERVAL '90 days' THEN 'active' 
        ELSE 'inactive' 
    END AS account_status
-- 记得补充你的表名、分组/排序子句
FROM your_table_name
GROUP BY accountid, customer
ORDER BY accountid, MAX(date) DESC;

方案2：把interval转成天数数值比较

如果你更习惯基于「距离当前的天数」判断，可以用EXTRACT(DAY FROM ...)把interval类型转换成整数天数，再和90做数值比较：

SELECT DISTINCT ON (accountid) 
    accountid, 
    customer, 
    round(AVG(qty),2) AS average_order_qty, 
    ROUND(AVG(total),2) AS avg_order_rev, 
    (CURRENT_DATE - MAX(date)) as days_since_order,
    CASE 
        WHEN EXTRACT(DAY FROM (CURRENT_DATE - MAX(date))) <= 90 THEN 'active' 
        ELSE 'inactive' 
    END AS account_status
FROM your_table_name
GROUP BY accountid, customer
ORDER BY accountid, MAX(date) DESC;

小提醒 #

你原来的代码里重复写了(CURRENT_DATE - MAX(date)) as active，这个字段名和CASE的结果字段重名了，记得要么改名要么删掉重复项哦！

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