如何将Oracle的ADD_MONTHS函数查询迁移至PostgreSQL

原语句逻辑说明 #

你提供的Oracle语句作用为：给日期字段start_billdate累加processed_num * periodvalue个月份，其中to_date(to_char(start_billdate,'DD-MM-YYYY'),'DD-MM-YYYY')属于冗余操作，若start_billdate本身是DATE类型，可直接传入函数无需格式转换。

PostgreSQL适配方案 #

PostgreSQL没有原生内置和Oracle行为完全一致的ADD_MONTHS函数，可根据场景选择以下两种实现方式：

方案1：基础原生写法（无严格兼容需求场景） #

如果不需要对齐Oracle特殊的月末日期跳转逻辑，直接通过日期加月份间隔的方式实现：

-- 写法1：使用make_interval函数（更规范，避免字符串拼接）
start_billdate + make_interval(months => (processed_num * periodvalue)::int)

-- 写法2：字符串转间隔类型
start_billdate + (processed_num * periodvalue || ' month')::interval

注意：该写法和Oracle行为差异点：例如Oracle中ADD_MONTHS('2024-02-29',1)返回2024-03-31，而原生间隔加法返回2024-03-29。

方案2：自定义函数（完全兼容Oracle行为） #

如果需要严格对齐Oracle ADD_MONTHS的月末跳转规则，先创建自定义函数：

CREATE OR REPLACE FUNCTION add_months(p_date date, p_months integer)
RETURNS date AS $$
DECLARE
    v_target_date date;
BEGIN
    v_target_date := p_date + make_interval(months => p_months);
    -- 匹配Oracle逻辑：原日期是月末/目标月天数不足原日期日数时，返回目标月最后一天
    IF p_date = (date_trunc('month', p_date) + INTERVAL '1 month - 1 day')::date
        OR EXTRACT(day FROM p_date) > EXTRACT(day FROM v_target_date)
    THEN
        RETURN (date_trunc('month', v_target_date) + INTERVAL '1 month - 1 day')::date;
    END IF;
    RETURN v_target_date;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

创建完成后直接替换原语句即可，和Oracle使用方式完全一致：

add_months(start_billdate, processed_num * periodvalue)

特殊场景处理 #

如果start_billdate是字符串格式存储的非标准日期，先转成日期类型再传入函数：

add_months(to_date(start_billdate::text, 'DD-MM-YYYY'), processed_num * periodvalue)

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