ClickHouse DB::Exception: Data type cannot be promoted

The "DB::Exception: Data type cannot be promoted" error occurs when ClickHouse attempts to widen (promote) a data type to a larger variant but no valid promotion exists for that type. This most commonly appears with SummingMergeTree and AggregatingMergeTree engines, where ClickHouse tries to promote numeric column types during merges to avoid overflow. If the type cannot be promoted — because it is already the widest in its family or because it's a non-numeric type — this error is raised.

Impact

The table creation or merge operation fails. In SummingMergeTree tables, this can prevent background merges from completing, leading to an accumulation of parts and degraded query performance.

Common Causes

1. Non-numeric column in SummingMergeTree sum columns — SummingMergeTree sums numeric columns during merges, and non-summable types like String or Date cannot be promoted.
2. Already-widest type in the family — Int128, UInt128, Int256, UInt256, or Decimal256 have no wider promotion target.
3. Attempting to promote Float types — while ClickHouse can promote Float32 to Float64, Float64 has no promotion target.
4. Complex types in aggregation contexts — Array, Tuple, or Map types used where type promotion is expected.
5. AggregatingMergeTree with incorrect column types — columns that should hold AggregateFunction(...) states but are declared with plain types.

Troubleshooting and Resolution Steps

1. Identify which column triggered the error. The error message names the type that cannot be promoted.

2. For SummingMergeTree, specify which columns to sum. By default, SummingMergeTree sums all numeric columns not in the key. Restrict it to only the intended columns:

   CREATE TABLE t (
       key UInt64,
       value Int64,
       label String
   ) ENGINE = SummingMergeTree(value)  -- only sum 'value'
   ORDER BY key;
   

3. Use a wider numeric type from the start. If promotion is needed, pre-select a type with room to grow:

   -- Use Int64 instead of Int32 for sum columns
   CREATE TABLE t (
       key UInt64,
       counter Int64
   ) ENGINE = SummingMergeTree(counter)
   ORDER BY key;
   

4. Use AggregateFunction types for AggregatingMergeTree:

   CREATE TABLE t (
       key UInt64,
       sum_state AggregateFunction(sum, Int64)
   ) ENGINE = AggregatingMergeTree()
   ORDER BY key;
   

5. Exclude non-promotable columns from aggregation. If a column should not participate in summing or aggregation, make sure it's part of the key or explicitly excluded:

   CREATE TABLE t (
       date Date,
       id UInt64,
       amount Decimal(18, 4),
       description String  -- Not summed because we specify sum columns
   ) ENGINE = SummingMergeTree(amount)
   ORDER BY (date, id);
   

6. Check for Float64 limitations. If you have a Float64 column that needs to participate in summing, it works but cannot be promoted further. This is usually fine for SummingMergeTree, as the summation happens within the existing type.

Best Practices

• Always explicitly specify the sum columns in SummingMergeTree(col1, col2, ...) rather than relying on the default behavior of summing all numeric columns.
• Use Int64 or Decimal types for columns that will be summed — they provide a good balance of range and precision.
• For AggregatingMergeTree tables, always use AggregateFunction(...) types for aggregation columns.
• Design your table schema so that non-numeric columns are part of the sorting key or are explicitly excluded from aggregation.

Frequently Asked Questions

Q: What does "type promotion" mean in ClickHouse?
A: Type promotion (or widening) is when ClickHouse automatically converts a value to a larger type to prevent overflow during operations. For example, summing Int32 values might promote the accumulator to Int64 to avoid overflow. The DATA_TYPE_CANNOT_BE_PROMOTED error means this widening is impossible for the given type.

Q: Does this error only affect SummingMergeTree?
A: It most commonly appears with SummingMergeTree, but can also surface with AggregatingMergeTree, certain expressions, and any context where ClickHouse needs to promote a type.

Q: Can I sum Decimal columns in SummingMergeTree?
A: Yes. Decimal types support summation. However, Decimal256 cannot be promoted further, so if overflow is a concern, ensure your precision is sufficient.

Q: What if I need to sum a column that's already UInt64?
A: ClickHouse can promote UInt64 to UInt128 or Int128 during summation. If you're using the widest available type (like UInt256), no further promotion is possible, but overflow at that scale is extremely unlikely.