PostgreSQL报类型不匹配错误：date列赋值text类型问题排查

错误原因 #

你遇到的ERROR: column "portfolio_end_date" is of type date but expression is of type text错误，本质是临时表tmp中的portfolio_end_date字段类型被PostgreSQL默认推断为text，而目标表ods2.project_portfolios的同名字段是date类型，类型不兼容导致更新操作失败。

具体来说，你的UPDATE语句里的VALUES子句中，portfolio_end_date对应的位置是NULL——PostgreSQL无法自动判断这个NULL的具体数据类型，会默认将其归类为text类型。虽然你给其他日期字段加了::date强制转换，但未对这个NULL做类型指定，最终导致临时表与目标表的字段类型不匹配。

解决方法 #

有两种简单的修复方式：

方法一：给NULL显式指定date类型 #

将VALUES子句中的NULL改为NULL::date，强制PostgreSQL将该值识别为date类型：

UPDATE ods2.project_portfolios as tbl 
set id_portfolio = tmp.id_portfolio,
id_project_link = tmp.id_project_link,
portfolio_start_date = tmp.portfolio_start_date,
portfolio_end_date = tmp.portfolio_end_date, 
id_employee = tmp.id_employee,
portfolio_changed_date = tmp.portfolio_changed_date,
portfolio_quarter = tmp.portfolio_quarter,
end_month = tmp.end_month,
historicity_time = tmp.historicity_time
from (
values (
    491533570142,
    25, 
    '2022-07-01'::date,
    NULL::date, -- 显式指定NULL为date类型
    51,
    '2023-02-28 14:27:24'::timestamp,
    NULL,
    '2022-07-01'::date,
    '2023-03-20 23:00:16'::timestamp)
) as tmp(
    id_portfolio, id_project_link, portfolio_start_date, portfolio_end_date, id_employee,
    portfolio_changed_date, portfolio_quarter, end_month, historicity_time)
where tbl.id_portfolio = tmp.id_portfolio and tbl.historicity_time between 
'2023-05-25 00:00:00' and '2023-05-25 23:59:59';

方法二：在临时表字段定义中显式声明类型 #

在tmp表的字段列表里，给portfolio_end_date加上date类型声明：

) as tmp(
    id_portfolio, id_project_link, portfolio_start_date, portfolio_end_date date, id_employee,
    portfolio_changed_date, portfolio_quarter, end_month, historicity_time)

这样PostgreSQL会按照指定类型处理该字段的NULL值，避免自动推断错误。

内容的提问来源于stack exchange，提问作者Юра Ким