PostgreSQL在INSERT..SELECT中修改或检查列数据类型触发AS语法错误

你触发语法错误的核心原因是：PostgreSQL 不支持直接在普通物理表的别名后通过AS t(列名 数据类型, ...)的语法强制转换列数据类型，该语法仅适用于返回记录集的函数、VALUES子句、子查询的别名定义场景，你在已经指定别名t1的物理表后额外加AS类型定义，不符合语法规范。

场景1：仅需要转换列数据类型 #

直接在SELECT子句中对每列做显式类型转换即可，可使用::类型转换符或者CAST函数，正确SQL示例如下：

INSERT INTO table_name( a, b, c, d) 
SELECT 
  t1.a::bigint, 
  t1.b::character varying, 
  t1.c::numeric, 
  t1.d::numeric 
FROM table_name t1;

场景2：需要先校验原始列类型再转换 #

如果需要先校验原始列的类型是否符合预期，不符合就终止执行，可以配合pg_typeof函数做判断：

INSERT INTO table_name( a, b, c, d) 
SELECT 
  CASE WHEN pg_typeof(t1.a) = 'bigint'::regtype THEN t1.a ELSE RAISE EXCEPTION '列a数据类型不匹配预期' END::bigint,
  t1.b::character varying,
  t1.c::numeric,
  t1.d::numeric 
FROM table_name t1;

• 显式类型转换遇到无法转换的值（比如非数字格式的字符串转numeric）会直接抛出错误终止执行，PostgreSQL 12及以上版本可以使用TRY_CAST(列名 AS 目标类型)函数，转换失败时返回NULL避免执行中断。
• 你当前的SQL是往同一张表插入数据，需要提前处理主键、唯一约束的冲突问题，可以补充ON CONFLICT子句定义冲突处理逻辑。

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