如何修复DBeaver查询AWS Redshift时的[500310]类型转换错误？

问题现象 #

在DBeaver中查询AWS Redshift数据时，单独执行两条SELECT语句均正常，但使用UNION ALL合并后触发报错，即使已显式声明所有字段的数据类型。涉及表字段：postl_st_cd（varchar）、cnt（integer）、var1（boolean）、var2（varchar）。

错误信息 #

com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: could not convert type "unknown" to numeric because of modifier;
    at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
    at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.moveThroughMetadata(Unknown Source)
    at com.amazon.redshift.client.PGMessagingContext.getNoData(Unknown Source)
    at com.amazon.redshift.client.PGClient.directExecuteExtraMetadataWithMessage(Unknown Source)
    at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
    at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
    at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

示例语句 #

示例1（报错的UNION ALL语句） #

select 
postl_st_cd::varchar as state,
(case when cnt >=1 and (var1 is true or var2 in('A','B','C','D')) then 1 else 0 end)::integer as cnt_rc
from Table
union all
select 
postl_st_cd::varchar as state,
0::integer as cnt_rc
from Table

示例2（正常的分开执行语句） #

select 
postl_st_cd::varchar as state,
(case when cnt >=1 and (var1 is true or var2 in('A','B','C','D')) then 1 else 0 end)::integer as cnt_rc
from Table


select 
postl_st_cd::varchar as state,
0::integer as cnt_rc
from Table

解决方法 #

报错核心原因是Redshift在UNION ALL时对两边字段的类型/长度推导不一致，即使显式转换仍可能出现"unknown"类型识别问题，可尝试以下方案：

• 给varchar指定明确长度：Redshift中若仅写::varchar不指定长度，不同查询分支可能推导不同的默认长度，导致UNION时类型不匹配。修改为指定长度的转换，比如postl_st_cd::varchar(2) as state（根据实际数据长度调整）。
• 统一类型声明方式：确保UNION ALL两边的cnt_rc字段类型完全一致，用CAST函数替代简写的::转换，消除类型推导歧义：

  -- 第一个SELECT的cnt_rc
  CAST(CASE WHEN cnt >=1 AND (var1 IS TRUE OR var2 IN('A','B','C','D')) THEN 1 ELSE 0 END AS INTEGER) AS cnt_rc
  -- 第二个SELECT的cnt_rc
  CAST(0 AS INTEGER) AS cnt_rc
  

• 检查表字段数据：确认cnt字段无隐式类型转换异常（比如存储了字符串形式的数字），var1/var2字段无异常值导致CASE表达式类型推导出错。

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