Redshift多同长度varchar列插入时，如何捕获超长报错列名？

方法1：用try_cast快速定位违规列和行 #

直接在查询中对每个目标列做长度校验，一次性找出所有存在超长度值的行及对应列，比单独查max(length())效率更高：

SELECT
    id, -- 用表的唯一键标识违规行
    STRING_AGG(
        CASE WHEN length(col1) > 32 THEN 'col1' END,
        ', '
    ) AS over_length_columns
FROM table2
WHERE length(col1) > 32 
   OR length(col2) > 32 
   OR length(col3) > 32 -- 列出所有目标varchar列
GROUP BY id
HAVING STRING_AGG(
        CASE WHEN length(col1) > 32 THEN 'col1' END,
        ', '
    ) IS NOT NULL;

方法2：利用Redshift系统视图提取错误详情 #

Redshift的svl_insert_errors系统视图会记录INSERT操作的错误细节，执行失败的INSERT后直接查询即可获取违规列名：

SELECT
    query,
    line_number,
    colname, -- 直接显示触发错误的列名
    err_code,
    err_reason
FROM svl_insert_errors
WHERE query = (SELECT last_query_id());

注：需确保当前用户有svl_insert_errors的访问权限，可通过GRANT SELECT ON svl_insert_errors TO your_user;授权。

方法3：封装预校验函数（复用性强） #

如果频繁遇到这类问题，可编写自定义函数批量检查指定表的同长度varchar列：

CREATE OR REPLACE FUNCTION check_varchar_lengths(schema_name varchar, table_name varchar, target_length int)
RETURNS TABLE(row_id varchar, over_length_cols varchar) AS $$
DECLARE
    col_record record;
    check_sql varchar;
BEGIN
    check_sql := 'SELECT id AS row_id, STRING_AGG(';
    FOR col_record IN 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = schema_name 
          AND table_name = table_name 
          AND data_type = 'character varying' 
          AND character_maximum_length = target_length
    LOOP
        check_sql := check_sql || 'CASE WHEN length(' || col_record.column_name || ') > ' || target_length || ' THEN ''' || col_record.column_name || ''' END, ';
    END LOOP;
    check_sql := LEFT(check_sql, LENGTH(check_sql)-2) || ', '', '') AS over_length_cols FROM ' || schema_name || '.' || table_name || ' GROUP BY id HAVING over_length_cols IS NOT NULL';
    
    RETURN QUERY EXECUTE check_sql;
END;
$$ LANGUAGE plpgsql;

调用示例：

SELECT * FROM check_varchar_lengths('public', 'table2', 32);

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