ClickHouse DB::Exception: Incompatible columns in UNION or INSERT

The "DB::Exception: Incompatible columns" error fires when ClickHouse cannot reconcile the data types of columns across UNION ALL branches, or between the source data and the target table in an INSERT. Each positional column across branches (or between source and target) must have compatible types, and ClickHouse is strict about what it considers compatible.

Impact

The query or INSERT fails entirely. In UNION ALL queries this prevents you from combining result sets, and in INSERT ... SELECT pipelines it blocks data from reaching the target table. This can stall reports, dashboards, and ETL jobs that rely on combining data from multiple sources.

Common Causes

1. UNION ALL branches with different column types — for example, one branch returns UInt32 and another returns String for the same positional column.
2. INSERT ... SELECT with mismatched types — the SELECT produces types that cannot be implicitly converted to the target table's column types.
3. Nullable vs non-Nullable differences — one branch produces Nullable(Int64) and another produces Int64.
4. Date vs DateTime vs String confusion — mixing date-like types across branches without explicit casting.
5. Enum type mismatches — different Enum definitions across branches that cannot be unified.

Troubleshooting and Resolution Steps

1. Identify which column is incompatible. The error message names the column position and the conflicting types.

2. Check types across all UNION branches. Run each branch separately and inspect the output types:

   SELECT toTypeName(col1), toTypeName(col2) FROM (first_branch) LIMIT 1;
   SELECT toTypeName(col1), toTypeName(col2) FROM (second_branch) LIMIT 1;
   

3. Cast columns to a common type explicitly:

   SELECT toInt64(id), name FROM table_a
   UNION ALL
   SELECT toInt64(id), name FROM table_b;
   

4. Align Nullable types. If one branch returns Nullable and another doesn't, wrap both:

   SELECT toNullable(col) FROM branch_a
   UNION ALL
   SELECT col FROM branch_b;  -- already Nullable
   

5. Fix INSERT ... SELECT type mismatches. Cast in the SELECT to match the target table types:

   INSERT INTO target_table
   SELECT
       toUInt32(id),
       toString(name),
       toDate(date_string)
   FROM source_table;
   

6. Use UNION ALL with SETTINGS union_default_mode = 'DISTINCT' carefully. Changing the union mode doesn't fix type mismatches, but be aware that UNION DISTINCT imposes additional requirements on comparable types.

Best Practices

• Always explicitly cast columns in UNION ALL queries to ensure type consistency across all branches.
• When writing INSERT ... SELECT, run the SELECT independently first and compare toTypeName() results against the target table's DESCRIBE output.
• Use a consistent type convention across tables that are frequently UNIONed — for example, always use Int64 for IDs rather than mixing Int32 and Int64.
• Document expected types for each position in UNION queries, especially in generated SQL.

Frequently Asked Questions

Q: Does ClickHouse ever implicitly convert types in UNION ALL?
A: ClickHouse will find a common supertype for compatible type pairs (e.g., Int32 and Int64 unify to Int64). But fundamentally incompatible types like String and Int32 have no common supertype and will trigger this error.

Q: Can I mix Nullable and non-Nullable columns in a UNION?
A: Yes, ClickHouse promotes the non-Nullable side to Nullable automatically. However, if the base types are incompatible, you still get an error.

Q: How do I handle Enum mismatches across UNION branches?
A: The simplest approach is to cast Enum columns to String in all branches: SELECT CAST(enum_col AS String) FROM .... This avoids Enum definition conflicts.

Q: Can I use CAST in INSERT ... SELECT to fix type issues?
A: Yes. You can cast in the SELECT clause, or ClickHouse will apply implicit conversion for some compatible type pairs. For incompatible types, explicit CAST or conversion functions like toString(), toInt64(), etc. are required.