Redshift从外部表插入记录到内部表时published_date字段报类型不匹配错误

报错本质是INSERT语句的返回列和目标表jatinanalysis的字段类型、顺序未对齐，即便已经对published_date做了CAST转换，也可能因为列顺序错位、外部表嵌套字段的转换规则不兼容触发该问题。

操作步骤 #

• 显式声明INSERT的目标字段列表
  这是最常见的触发原因：当前写的插入语句没有在表名后指定要写入的列，Redshift会默认按目标表的列定义顺序，和SELECT返回的列一一匹配。只要目标表的列顺序和SELECT的列顺序差一位，就会出现「目标字段是timestamp，传入的表达式是varchar」的报错。
  正确写法示例：

INSERT INTO jatinanalysis (
  title,
  link,
  author,
  published_date,
  category
)
SELECT 
  b.title,
  b.link,
  b.author,
  b.published_date,
  category
FROM jatinspectrum.extable a, a.enteries b, b.category category;

• 替换裸CAST为显式格式的时间转换逻辑
  从Spectrum外部表的嵌套结构中取出的published_date是字符串类型，直接写cast(b.published_date as timestamp)时，如果字符串格式不符合Redshift默认的timestamp解析规则，转换逻辑会失效，最终返回结果仍是varchar类型，触发不匹配。
  这里要换成指定格式的to_timestamp函数，根据实际的时间字符串格式调整参数，通用写法参考：

-- 适配'YYYY-MM-DD HH24:MI:SS'格式的时间字符串
to_timestamp(b.published_date, 'YYYY-MM-DD HH24:MI:SS')::timestamp without time zone

注意：to_timestamp默认返回带时区的timestamp类型，和目标表中timestamp without time zone的字段定义不兼容，转换后必须显式声明为不带时区的类型。

• 插入前先做类型校验
  正式执行插入前，先单独查询每一列的实际类型，和目标表的字段定义逐一比对，提前排查隐藏的类型问题：

SELECT 
  pg_typeof(b.title) as title_type,
  pg_typeof(b.link) as link_type,
  pg_typeof(b.author) as author_type,
  pg_typeof(to_timestamp(b.published_date, 'YYYY-MM-DD HH24:MI:SS')::timestamp without time zone) as published_date_type,
  pg_typeof(category) as category_type
FROM jatinspectrum.extable a, a.enteries b, b.category category
LIMIT 1;

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