如何快速定位Redshift表中长度不足的字符型列？

问题背景 #

我有一张包含大量varchar(65535)类型列的表，按优化建议创建了字段类型更合理的新表，但执行insert into newtable select * from oldtable时，出现部分新列长度分配不足的报错，错误信息如下：

Query 201601 caught Exception: #

error: Value too long for character type
code: 8001
context: Value too long for type character varying(20)
query: 201601
location: string.cpp:219
process: query1_125_201601 [pid=21317] #

由于列数量众多，逐个排查效率极低，以下是几种快速定位问题列的方法：

方法1：通过系统表批量对比列长度 #

直接查询Redshift系统表，结合原表实际数据的最大长度，对比新表的列定义长度，一键找出不符合的列：

SELECT 
    c_old.column_name,
    (SELECT MAX(LENGTH(oldtable."${c_old.column_name}")) FROM oldtable) AS actual_max_length,
    CAST(SUBSTRING(c_new.data_type, POSITION('(' IN c_new.data_type)+1, POSITION(')' IN c_new.data_type)-POSITION('(' IN c_new.data_type)-1) AS INT) AS new_column_length
FROM 
    information_schema.columns c_old
JOIN 
    information_schema.columns c_new 
    ON c_old.column_name = c_new.column_name
WHERE 
    c_old.table_name = 'oldtable'
    AND c_new.table_name = 'newtable'
    AND c_old.data_type LIKE 'character varying%'
    AND c_new.data_type LIKE 'character varying%'
HAVING 
    actual_max_length > new_column_length
ORDER BY 
    actual_max_length DESC;

该查询会返回所有原表实际最大长度超过新表定义长度的列，直接定位问题点。

方法2：分批次插入缩小排查范围 #

如果系统表查询较慢，可采用分批次插入的方式逐步定位：

• 选取部分列执行插入：insert into newtable (col1, col2, ..., col10) select col1, col2, ..., col10 from oldtable
• 若报错，将这批列拆分后继续测试；若成功，换下一批列重复操作
• 逐步缩小范围，直到找到具体的问题列

方法3：用TRY_CAST标记转换失败的列 #

利用Redshift的TRY_CAST函数，在查询时直接标记出无法转换到新表类型的列：

SELECT 
    *,
    -- 为每个varchar列添加检查，替换成你的列名和新表定义长度
    CASE WHEN TRY_CAST(col1 AS VARCHAR(20)) IS NULL THEN 'col1' ELSE '' END AS error_col1,
    CASE WHEN TRY_CAST(col2 AS VARCHAR(50)) IS NULL THEN 'col2' ELSE '' END AS error_col2
    -- 其他列按此格式依次添加
FROM oldtable
WHERE 
    TRY_CAST(col1 AS VARCHAR(20)) IS NULL
    OR TRY_CAST(col2 AS VARCHAR(50)) IS NULL
    -- 其他列按此格式添加OR条件
LIMIT 10;

这个语句会返回存在转换失败的行，并明确标记出对应的问题列。如果列数量极多，可以写脚本批量生成这些检查语句。

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