SQL Server迁数据到PostgreSQL用UNION ALL插入timestamp报错咨询

问题核心原因 #

这个问题是PostgreSQL和SQL Server的类型推导逻辑差异导致的，和DBeaver无关：

1. 单条INSERT ... SELECT场景下，PostgreSQL会直接参考目标表的列类型，将SELECT子句中无明确类型的字符串字面量（初始为unknown类型）隐式转换为对应目标列类型，因此你单条插入可以正常执行。
2. 使用UNION ALL拼接多个查询时，PostgreSQL会优先完成UNION ALL结果集的类型统一，这一阶段不会参考外层INSERT的目标列类型：多个分支的无类型字符串字面量会被统一推导为text类型，而PostgreSQL的隐式转换规则不允许text类型直接隐式转换为timestamp类型，因此会抛出类型不匹配错误。

可直接落地的解决方案 #

• 方案1：仅给UNION ALL第一个分支的时间字段加显式类型转换，后续分支会自动继承类型，无需逐行修改。另外PostgreSQL不需要加SQL Server风格的N前缀标识Unicode字符串，默认varchar类型就支持Unicode：

INSERT INTO public.invoices(InvoiceNo,TerminalID,InvoiceDate,Description)
SELECT 7 as invoiceno , 5 as terminalid , '2018-10-24 21:29:00'::timestamp as invoicedate , 'Coffe and cookie' as description  
UNION ALL
SELECT 1000, 5 , '2018-10-24 21:29:00' , 'Tea and crumpets'

• 方案2：改用批量VALUES语法，更适合小批量数据插入：

INSERT INTO public.invoices(InvoiceNo,TerminalID,InvoiceDate,Description)
VALUES
(7,5,'2018-10-24 21:29:00'::timestamp,'Coffe and cookie'),
(1000,5,'2018-10-24 21:29:00','Tea and crumpets')

• 方案3：如果仅做少量数据迁移，直接用DBeaver自带的数据迁移功能更省事：选中SQL Server源表右键选择「导出数据」，目标选择PostgreSQL的对应表，工具会自动处理类型映射，不需要手动写插入脚本。

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