PostgreSQL v17递归CTE报错：relation 'basic_monthly_payments'不存在求助

核心错误分析 #

1. 递归CTE语法错误：PostgreSQL中使用递归CTE必须在WITH后添加RECURSIVE关键字，而非给单个CTE命名前加。原代码未加该关键字，导致递归引用时找不到basic_monthly_payments关系。
2. 递归列缺失依赖字段：递归部分需要用到next_date判断终止条件，但基础查询未将next_date纳入返回列，导致无法引用。
3. 窗口函数排序逻辑错误：LEAD函数的ORDER BY应按start_date排序，而非customer_id，否则无法正确获取用户下一次订阅的日期。
4. 递归CTE内禁止使用ORDER BY：递归CTE的UNION ALL后不能直接加ORDER BY，排序应放在最终的SELECT语句中。
5. 日期类型不匹配：payment_date + INTERVAL '1 month'返回的是带时间的类型，需转为DATE类型再与LEAST里的日期值比较。

修正后的SQL代码 #

WITH RECURSIVE
subscription_info AS ( 
    SELECT
        s.customer_id,
        s.plan_id,
        p.plan_name,
        s.start_date,
        -- 修正窗口函数排序逻辑，按订阅日期取后续订阅时间
        LEAD(s.start_date) OVER(
                PARTITION BY s.customer_id
                ORDER BY s.start_date
            ) AS next_date,
        p.price
    FROM
        subscriptions AS s
            LEFT JOIN
                plans AS p
                    ON s.plan_id = p.plan_id
    WHERE
        p.plan_id != 0 AND
        EXTRACT(YEAR FROM start_date) = '2020'
),

basic_monthly_payments AS ( 
    SELECT
        customer_id,
        plan_id,
        plan_name,
        start_date AS payment_date,
        price AS amount,
        -- 必须将next_date纳入基础查询列，供递归部分使用
        next_date
    FROM
        subscription_info
    WHERE
        plan_id = 1
    
    UNION ALL
    
    SELECT
        customer_id,
        plan_id,
        plan_name,
        -- 转换为DATE类型，避免类型不匹配
        (payment_date + INTERVAL '1 month')::DATE AS payment_date,
        amount,
        next_date
    FROM
        basic_monthly_payments
    WHERE
        (payment_date + INTERVAL '1 month')::DATE < LEAST('2021-01-01'::DATE, COALESCE(next_date, '2021-01-01'::DATE))
)
-- 将排序移到最终查询
SELECT
    customer_id,
    plan_id,
    plan_name,
    payment_date,
    amount
FROM
    basic_monthly_payments
ORDER BY customer_id, payment_date;

额外说明 #

• 使用COALESCE(next_date, '2021-01-01'::DATE)处理用户未更换订阅的情况，确保递归在2020年底终止。
• 所有日期计算统一转为DATE类型，避免因时间部分导致的比较误差。

内容的提问来源于stack exchange，提问作者Jacob D'Aurizio