为何出现SQL错误[42883]：trunc(timestamp without time zone)函数不存在？

错误现象 #

执行UPDATE语句时触发如下错误：

SQL Error [42883]: ERROR: function trunc(timestamp without time zone) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 2833

报错核心代码片段：

WHERE edv.ztab0000mn.cdc_oper_cd = 'D' AND DATE(edv.ztab0000mn.cdc_dt) = TRUNC(TO_TIMESTAMP('<DATA_EFF_STRT_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF')::TIMESTAMP WITHOUT TIME ZONE) AND edv.ztab0000mn.load_dt = TO_TIMESTAMP('<LOAD_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF') 

尝试操作及结果：

• 移除WITHOUT TIME ZONE后仍报相同错误
• 将变量占位符替换为列名data_eff_strt_dt时，提示column "data_eff_strt_dt" does not exist and cannot be referenced in this part of the query

完整查询语句：

UPDATE edv.core_cust_rep_capc_hs
SET load_end_dt = stg.load_dt, data_eff_end_dt = stg.data_eff_strt_dt
FROM edv.core_cust_rep_capc_hs AS dv
INNER JOIN (SELECT DISTINCT
    COALESCE(edv.but000.bpext, '-1') AS core_cust_id, COALESCE(edv.ztab0000mn.record_id, '[NULL IN SOURCE]') AS src_rcd_id, edv.ztab0000mn.load_dt AS load_dt, edv.ztab0000mn.cdc_dt AS data_eff_strt_dt, edv.ztab0000mn.data_src_nm AS data_src_nm, edv.ztab0000mn.parent_id AS src_prnt_id, edv.ztab0000mn.zzfld00009s AS bus_ptnr_rlt_type_cd, edv.ztab0000mn.zzfld00009t AS rlt_grp_nbr, edv.ztab0000mn.zzfld00009v AS src_data_eff_end_dt, edv.ztab0000mn.zzfld00009u AS src_data_eff_strt_dt, edv.ztab0000mn.zzfld0000c8 AS poa_rlt_cncl_rsn_cd, edv.ztab0000mn.zzfld0000ce AS ot_rsn_txt, edv.ztab0000mn.zzfld0000cf AS stip_txt, edv.ztab0000mn.zzfld0000cg AS rep_capc_stat_cd, edv.ztab0000mn.zzfld0000ei AS sgn_ct, edv.ztab0000mn.zzfld0000eo AS ogc_rvw_dt, edv.ztab0000mn.zzfld0000kw AS fdry_agr_nbr, edv.ztab0000mn.zzfld0000mo AS rlt_bus_ptnr_nbr, edv.ztab0000mn.zzfld0000n0 AS sap_src_cd, edv.ztab0000mn.zzfld0000dr AS url_l, edv.ztab0000mn.zzfld0000ks AS rcd_ofc, TO_TIMESTAMP('9999-12-31', 'YYYY-MM-DD') AS data_eff_end_dt, TO_TIMESTAMP('9999-12-31', 'YYYY-MM-DD') AS load_end_dt, md5(CONCAT_WS('', TRIM(edv.but000.bpext :: varchar), '~~', TRIM(edv.ztab0000mn.record_id :: varchar), '~~', TRIM(edv.ztab0000mn.parent_id :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld00009s :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld00009t :: varchar), '~~', TO_CHAR(edv.ztab0000mn.zzfld00009v, 'YYYY-MM-DD HH24:MI:SS'), '~~', TO_CHAR(edv.ztab0000mn.zzfld00009u, 'YYYY-MM-DD HH24:MI:SS'), '~~', TRIM(edv.ztab0000mn.zzfld0000c8 :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000ce :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000cf :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000cg :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000ei :: varchar), '~~', TO_CHAR(edv.ztab0000mn.zzfld0000eo, 'YYYY-MM-DD HH24:MI:SS'), '~~', TRIM(edv.ztab0000mn.zzfld0000kw :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000mo :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000n0 :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000dr :: varchar), '~~', TRIM(edv.ztab0000mn.zzfld0000ks :: varchar))) AS hash_dif, ROW_NUMBER() OVER (PARTITION BY COALESCE(edv.ztab0000mn.record_id, '[NULL IN SOURCE]') ORDER BY edv.ztab0000mn.cdc_dt DESC, edv.ztab0000mn.ods_cre_dt DESC, edv.ztab0000mn.ods_upd_dt DESC) AS row_num_part
    FROM edv.ztab0000mn
    JOIN edv.but000
        ON (edv.ztab0000mn.parent_id = edv.but000.partner_guid AND edv.but000.bpext IS NOT NULL)
    JOIN edv.tb002
        ON (edv.but000.bu_group = edv.tb002.bu_group AND edv.tb002.spras = 'E' AND edv.tb002.txt15 = 'Customer' AND edv.tb002.cdc_oper_cd IN ('I', 'UN', 'D'))
    WHERE edv.ztab0000mn.cdc_oper_cd = 'D' AND DATE(edv.ztab0000mn.cdc_dt) = TRUNC(TO_TIMESTAMP(data_eff_strt_dt, 'YYYY-MM-DD HH24:MI:SS.FF')::TIMESTAMP WITHOUT TIME ZONE) AND edv.ztab0000mn.load_dt = TO_TIMESTAMP('<LOAD_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF') 
    ORDER BY edv.ztab0000mn.cdc_dt) AS stg
    ON COALESCE(stg.src_rcd_id, 'DFLT') = COALESCE(dv.src_rcd_id, 'DFLT')
    WHERE core_cust_rep_capc_hs.load_dt <> stg.load_dt AND core_cust_rep_capc_hs.load_end_dt = TO_TIMESTAMP('9999-12-31', 'YYYY-MM-DD') AND stg.row_num_part = 1 

问题原因分析 #

1. trunc函数参数不匹配：PostgreSQL中，trunc()函数默认仅支持numeric类型截断，处理时间类型时必须指定截断精度参数（如'day'）。直接传入timestamp类型且无精度参数时，数据库找不到匹配的函数签名，触发42883错误。
2. 列引用作用域错误：将变量替换为data_eff_strt_dt时报错，是因为该列是子查询SELECT语句中定义的别名，而WHERE子句属于子查询的FROM/JOIN执行阶段，此时无法识别SELECT中定义的别名，只能引用基础表的原始列。

解决办法 #

1. 修复trunc函数调用 #

将TRIM(timestamp)替换为以下两种方式之一：

• 方式一：使用DATE()函数直接提取日期部分，与左侧的DATE(edv.ztab0000mn.cdc_dt)保持逻辑一致：

  DATE(edv.ztab0000mn.cdc_dt) = DATE(TO_TIMESTAMP('<DATA_EFF_STRT_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF'))
  

• 方式二：给trunc()添加精度参数，明确截断到天：

  DATE(edv.ztab0000mn.cdc_dt) = TRUNC(TO_TIMESTAMP('<DATA_EFF_STRT_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF'), 'day')
  

2. 修复列引用错误 #

如果要匹配基础表的时间列，直接引用原始列名：

• 子查询中data_eff_strt_dt是edv.ztab0000mn.cdc_dt的别名，若要引用该值，在WHERE中需写edv.ztab0000mn.cdc_dt
• 若要匹配源表的其他时间列（如edv.ztab0000mn.zzfld00009u，你定义为src_data_eff_strt_dt），直接写原始列名即可：

  WHERE edv.ztab0000mn.cdc_oper_cd = 'D' 
    AND DATE(edv.ztab0000mn.cdc_dt) = DATE(edv.ztab0000mn.zzfld00009u)
    AND edv.ztab0000mn.load_dt = TO_TIMESTAMP('<LOAD_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF')
  

3. 完整修改后的核心WHERE片段示例 #

WHERE edv.ztab0000mn.cdc_oper_cd = 'D' 
  AND DATE(edv.ztab0000mn.cdc_dt) = DATE(TO_TIMESTAMP('<DATA_EFF_STRT_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF'))
  AND edv.ztab0000mn.load_dt = TO_TIMESTAMP('<LOAD_DT variable place holder>', 'YYYY-MM-DD HH24:MI:SS.FF')

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