Redshift SQL报错[XX000]：无效数字转换问题求助

问题背景 #

之前运行正常的Redshift SQL脚本突发报错：

Redshift SQL Error [XX000]: ERROR: Invalid digit, Value 'a', Pos 3, Type: Integer

未对脚本做任何改动，排查后未发现明显数据异常，移除语句中的::integer/::smallint强制转换后脚本可正常运行，原SQL代码如下：

select
    is_valid_json(feasibility_data) as feasibility_data_is_valid,
    is_valid_json(product_target_data) as product_target_data_is_valid,
    case
            when product_target_data_is_valid then nullif(trim(json_extract_path_text(product_target_data, 'target_group_id')), '')::integer 
    end as order_setup_target_group_id,
    case
        when product_target_data_is_valid then nullif(trim(json_extract_path_text(product_target_data, 'category_id')), '')::integer 
    end as order_setup_category_id,
    is_valid_json(product_configuration_data) as product_configuration_data_is_valid,
    case when product_configuration_data_is_valid then
        coalesce(
            nullif(trim(json_extract_path_text(product_configuration_data, 'behaviour_change_priced', 'action')), ''),
            nullif(trim(json_extract_path_text(product_configuration_data, 'behaviour_change', 'action')), '')
        )::smallint
    end as order_behaviour_action_id,
    case when product_configuration_data_is_valid then
        coalesce(
            nullif(trim(json_extract_path_text(product_configuration_data, 'behaviour_change_priced', 'noun')), ''),
            nullif(trim(json_extract_path_text(product_configuration_data, 'behaviour_change', 'noun')), '')
        )::smallint
    end as order_behaviour_noun_id,
    first_value(id) over(partition by order_id order by _fivetran_deleted, created_at asc rows between unbounded preceding and unbounded following) as first_id,
    --cpi_override
    case
        WHEN feasibility_data_is_valid THEN nullif(trim(json_extract_path_text(json_extract_path_text(feasibility_data, 'variables_used'),'cpi_override')),'')
    END as  order_cpi_override_raw,
    case
        WHEN order_cpi_override_raw = 'true' THEN true 
        WHEN order_cpi_override_raw = 'false' THEN false
        ELSE null
    END order_cpi_override,
    *
from 
    datawarehouse.project_setup_product_setups

报错原因 #

报错提示明确是遇到了非数字字符（'a'）无法转换为整数，说明即便JSON校验通过，提取出的字段值也可能包含非数字内容，比如字符串"12a"、"abc"，或是看似空串但实际存在不可见字符的情况。nullif(trim(...), '')只能处理空串，无法过滤这些非数字的有效字符串，强制转换时就会触发报错。

解决方案 #

方案1：使用safe_cast安全转换（推荐） #

Redshift的safe_cast函数会在转换失败时返回null，而非直接报错，完美适配这种场景。只需把原语句中的强制转换替换成safe_cast：

• 原写法：nullif(trim(json_extract_path_text(...)), '')::integer
• 修改后：safe_cast(nullif(trim(json_extract_path_text(...)), '') as integer)
• 同理，::smallint替换为safe_cast(...) as smallint

修改后的示例片段：

case
    when product_target_data_is_valid then safe_cast(nullif(trim(json_extract_path_text(product_target_data, 'target_group_id')), '') as integer)
end as order_setup_target_group_id

方案2：先校验纯数字再转换 #

通过正则表达式校验提取值是否为纯数字，只有符合条件的才进行转换，否则返回null：

case
    when product_target_data_is_valid then 
        case 
            when regexp_like(nullif(trim(json_extract_path_text(product_target_data, 'target_group_id')), ''), '^[0-9]+$') 
            then nullif(trim(json_extract_path_text(product_target_data, 'target_group_id')), '')::integer 
            else null 
        end
end as order_setup_target_group_id

方案3：从数据根源修复 #

运行查询排查包含非数字值的记录，确认是脏数据还是业务逻辑变更导致的内容异常，从源头修正数据：

select product_target_data
from datawarehouse.project_setup_product_setups
where is_valid_json(product_target_data) = true
and not regexp_like(nullif(trim(json_extract_path_text(product_target_data, 'target_group_id')), ''), '^[0-9]*$')

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