Oracle转PostgreSQL查询报错：operator does not exist: date + numeric

错误原因分析 #

报错ERROR: operator does not exist: date + numeric是因为PostgreSQL不支持DATE类型与NUMERIC类型直接相加，而Oracle允许将数值（代表天数）直接加到DATE上，这是两者语法的核心差异点。原语句中试图通过给epoch日期加计算后的天数来转换毫秒时间戳，该逻辑在PG里需要改用兼容的时间处理方式。

修正后的完整查询语句 #

(select PK_, 
        'Statement of Holdings' as NOTIFICATIONNAME_ , 
        CASE NOTIFICATIONSSTATUS_ 
             WHEN 'Processed'              THEN 'Succeeded' 
             WHEN 'Processed With Failure' THEN 'Failed' 
             WHEN 'Impacted'               THEN 'Succeeded'  
             WHEN 'Impact Failed'          THEN 'Failed' 
             WHEN 'Archived'               THEN 'Succeeded' 
             WHEN 'Prepared'               THEN 'Running' 
                                           ELSE 'Planned' 
        END as STATUS , 
        DIRECTION_, 
        UPDATEDATE_ as updatedate_, 
        -- 替换原Oracle时间转换逻辑，改用PG原生函数处理毫秒时间戳
        to_timestamp(UPDATEDATE_ / 1000) as MODIFICATIONDATE  
from atementOfHoldingsNotification_ 
where (DIRECTION_ is not null) and (updatedate_ > 1609462800000) ) 

union all 

(select PK_, 
        'Statement of Transactions' as NOTIFICATIONNAME_ , 
        CASE NOTIFICATIONSSTATUS_  
             WHEN 'Processed'              THEN 'Succeeded' 
             WHEN 'Processed With Failure' THEN 'Failed' 
             WHEN 'Impacted'               THEN 'Succeeded'  
             WHEN 'Impact Failed'          THEN 'Failed' 
             WHEN 'Archived'               THEN 'Succeeded' 
             WHEN 'Prepared'               THEN 'Running' 
                                           ELSE 'Planned' 
        END as STATUS , 
        DIRECTION_, 
        UPDATEDATE_ as updatedate_, 
        to_timestamp(UPDATEDATE_ / 1000) as MODIFICATIONDATE  
from entOfTransactionsNofitication_ 
where (DIRECTION_ is not null) and (updatedate_ > 1609462800000)) 

union all 

(select PK_, 
        'Statement of Pending Transactions' as NOTIFICATIONNAME_ , 
        CASE NOTIFICATIONSSTATUS_ 
             WHEN 'Processed'              THEN 'Succeeded' 
             WHEN 'Processed With Failure' THEN 'Failed' 
             WHEN 'Impacted'               THEN 'Succeeded'  
             WHEN 'Impact Failed'          THEN 'Failed' 
             WHEN 'Archived'               THEN 'Succeeded' 
             WHEN 'Prepared'               THEN 'Running'
                                           ELSE 'Planned' 
        END as STATUS , 
        DIRECTION_, 
        UPDATEDATE_ as updatedate_, 
        to_timestamp(UPDATEDATE_ / 1000) as MODIFICATIONDATE  
-- 注：原语句此处未完成，请补充实际表名与WHERE条件
from your_actual_table_name  
where (DIRECTION_ is not null) and (updatedate_ > 1609462800000))

关键修正点说明 #

1. 时间戳转换逻辑替换：

   • 原Oracle逻辑通过给epoch日期累加计算出的天数实现转换，PG不支持该语法；
   • PG兼容逻辑使用to_timestamp(UPDATEDATE_ / 1000)：该函数直接接受秒级时间戳，将毫秒时间戳除以1000即可转换为秒级，直接得到timestamp类型，逻辑更简洁且符合PG规范。

2. 若需将结果转为DATE类型而非TIMESTAMP，可追加类型转换：

   to_timestamp(UPDATEDATE_ / 1000)::date as MODIFICATIONDATE
   

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