Redshift跨表插入转类型时布尔报错：invalid input syntax for type boolean: ""

错误提示invalid input syntax for type boolean: ""，说明你的isdeleted字段中存在未被正确处理的空字符串（或空白字符串），导致转换布尔类型时失败。以下是修正后的SQL语句：

insert into main_table (
  select 
    case 
      when id in ('null', '') then null 
      else id::varchar(max) 
    end as id, 
    case 
      when date in ('null', '') then null 
      else date::timestamp 
    end as date, 
    case 
      when payment in ('null', '', 'None') then null 
      else payment::double precision 
    end as payment, 
    case 
      when isdeleted in ('null', '') then null
      when trim(isdeleted) = '' then null
      else 
        case trim(isdeleted)
          when 'true' then true
          when 'false' then false
          else null
        end
    end as isdeleted 
  from table_temp
);

关键修改点说明： #

• 简化各字段的空值判断逻辑，用in替代多个when，代码更简洁易读
• 给isdeleted新增trim(isdeleted) = ''判断，处理带有空格的空白字符串（比如' '）——这类值之前的逻辑没覆盖，会进入else分支导致转换失败
• 替换原有的replace+转int再转布尔的方式，直接通过case匹配字符串返回布尔值，避免中间转换环节可能出现的错误
• 增加else null兜底逻辑，防止遇到true/false之外的其他无效字符串时再次报错

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