PostgreSQL使用UNION ALL查询枚举列报类型转换错误如何解决

你遇到的报错是因为UNION/UNION ALL要求上下两个查询的对应位置字段数据类型完全一致，两张表的type、status字段属于不同的枚举类型，数据库无法自动完成隐式类型转换导致报错。

方案1：调整UNION ALL用法即可解决（优先推荐） #

不需要替换UNION ALL，只需要将枚举类型字段显式转换为通用的字符串类型即可，不同数据库的转换写法参考：

• PostgreSQL 示例：

SELECT
  id,
  type::VARCHAR AS type,
  status::VARCHAR AS status,
  -- 其余统一字段顺序、数量保持和下方查询完全一致
  'money_transfer' AS record_source
FROM money_transfer_table
UNION ALL
SELECT
  id,
  type::VARCHAR AS type,
  status::VARCHAR AS status,
  -- 其余字段和上方查询对应位置类型保持一致
  'invoice' AS record_source
FROM invoice_table;

• MySQL 示例：

SELECT
  id,
  CAST(type AS CHAR) AS type,
  CAST(status AS CHAR) AS status,
  'money_transfer' AS record_source
FROM money_transfer_table
UNION ALL
SELECT
  id,
  CAST(type AS CHAR) AS type,
  CAST(status AS CHAR) AS status,
  'invoice' AS record_source
FROM invoice_table;

注意事项：

• 两个SELECT语句的字段数量、字段顺序必须完全匹配
• 额外新增的record_source字段用于标记数据来源表，避免后续业务逻辑无法区分数据归属
• 无去重需求时固定使用UNION ALL，性能远高于带去重逻辑的UNION

方案2：保留枚举类型的替代方案 #

如果你需要保留枚举字段的原始类型，不需要合并为同名字段，可以使用全连接加恒假条件的方式实现，结果会保留两张表的所有字段，无关联数据的对应位置为NULL：

SELECT
  m.id AS money_transfer_id,
  m.type AS money_transfer_type,
  m.status AS money_transfer_status,
  -- 转账表其余字段
  i.id AS invoice_id,
  i.type AS invoice_type,
  i.status AS invoice_status
  -- 发票表其余字段
FROM money_transfer_table m
FULL OUTER JOIN invoice_table i ON 1=2;

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