RedShift使用APPEND FROM FILLTARGET同步表报错：列属性不匹配排查

问题场景 #

执行RedShift的APPEND命令从临时表向主表追加数据并自动补充目标表缺失列：

ALTER TABLE master.master_dbo.vendor_inspection_report APPEND FROM rab.rab_dbo.vendor_inspection_report_2023_12_19 FILLTARGET;

触发报错：

Execution Issue :  Columns don't match.
context:   Column "critical_defect_photos_captions" has different attributes in the source table and the target table. Columns with the same name must have the same attributes in both tables.

已确认两张表该列定义均为critical_defect_photos_captions character varying(4000) ENCODE bytedict,，需排查遗漏的差异点。

排查方向 #

• 列名大小写一致性：RedShift默认不区分列名大小写，但如果创建表时用双引号强制指定大小写，会导致表面名称一致但实际存储的大小写不同，APPEND操作会判定为不同列。
• 默认值差异：即使数据类型和编码一致，列的默认值不同也会触发错误。执行以下SQL对比：

  SELECT column_name, column_default 
  FROM information_schema.columns 
  WHERE table_name IN ('vendor_inspection_report', 'vendor_inspection_report_2023_12_19') 
    AND column_name = 'critical_defect_photos_captions';
  

• NOT NULL约束差异：源表和目标表的该列是否存在一方有NOT NULL约束、另一方没有的情况。执行SQL验证：

  SELECT column_name, is_nullable 
  FROM information_schema.columns 
  WHERE table_name IN ('vendor_inspection_report', 'vendor_inspection_report_2023_12_19') 
    AND column_name = 'critical_defect_photos_captions';
  

• 编码实际生效情况：虽然定义中指定了ENCODE bytedict，但可能存在后续编码被修改的情况。用RedShift系统视图查看实际编码：

  SELECT "column", type, encoding 
  FROM svv_columns 
  WHERE table_name IN ('vendor_inspection_report', 'vendor_inspection_report_2023_12_19') 
    AND "column" = 'critical_defect_photos_captions';
  

• 排序规则（Collation）差异：列的排序规则不同也会被判定为属性不匹配。执行以下SQL对比：

  SELECT column_name, collation_name 
  FROM information_schema.columns 
  WHERE table_name IN ('vendor_inspection_report', 'vendor_inspection_report_2023_12_19') 
    AND column_name = 'critical_defect_photos_captions';
  

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