ClickHouse DB::Exception: Cannot update column

The "DB::Exception: Cannot update column" error in ClickHouse occurs when an ALTER TABLE ... UPDATE mutation targets a column that cannot be modified. Columns that are part of the primary key, sorting key, or partition key are immutable because changing them would invalidate the physical data layout. The error code is CANNOT_UPDATE_COLUMN.

Impact

The mutation is rejected and no data is changed. This protects the integrity of the table's key structure. If you need to change the value of a key column, you must use an alternative approach such as inserting corrected rows and removing the old ones.

Common Causes

1. Updating a column in the ORDER BY clause -- sorting key columns define the physical order of data and cannot be updated in place.
2. Updating a primary key column -- primary key columns are used for index lookups and must remain unchanged.
3. Updating a partition key column -- partition keys determine how data is split into parts, and changing them would require moving data between partitions.
4. Updating a column used in the SAMPLE BY expression -- sampling keys are part of the data layout.
5. Updating a column of an unsupported type in certain contexts -- some column types or codecs may have restrictions in specific versions.

Troubleshooting and Resolution Steps

1. Identify which key the column belongs to:

   SELECT sorting_key, primary_key, partition_key, sampling_key
   FROM system.tables
   WHERE database = 'default' AND name = 'your_table';
   

2. Use INSERT + DELETE pattern instead of UPDATE for key columns:

   -- Insert corrected rows
   INSERT INTO your_table SELECT
       new_key_value AS key_column,
       other_columns
   FROM your_table
   WHERE key_column = old_value;
   
   -- Delete old rows
   ALTER TABLE your_table DELETE WHERE key_column = old_value;
   

3. For bulk corrections, use a new table and data migration:

   CREATE TABLE your_table_fixed AS your_table;
   
   INSERT INTO your_table_fixed
   SELECT
       if(key_col = 'wrong', 'correct', key_col) AS key_col,
       other_columns
   FROM your_table;
   
   RENAME TABLE your_table TO your_table_old, your_table_fixed TO your_table;
   

4. Consider using CollapsingMergeTree or VersionedCollapsingMergeTree. These engines allow "canceling" rows and inserting corrected versions:

   -- Cancel the old row (sign = -1)
   INSERT INTO your_table (key_col, value, sign) VALUES ('old_key', 123, -1);
   -- Insert the corrected row (sign = 1)
   INSERT INTO your_table (key_col, value, sign) VALUES ('new_key', 123, 1);
   

5. For non-key columns, verify the column name and table:

   -- This should work for non-key columns
   ALTER TABLE your_table UPDATE non_key_column = 'new_value' WHERE id = 5;
   

Best Practices

• Design your table schema with the understanding that key columns are immutable. Choose key columns that will not need to change.
• Use ReplacingMergeTree for scenarios where you need to update rows frequently -- insert the corrected version and let deduplication handle the rest.
• For tables where key corrections are occasionally needed, keep the INSERT + DELETE workflow documented and ready.
• Minimize the number of key columns to reduce the surface area of columns that cannot be updated.

Frequently Asked Questions

Q: Can I update any non-key column?
A: Generally yes. Non-key columns can be updated via ALTER TABLE ... UPDATE. The restriction applies specifically to columns in the primary key, sorting key, partition key, and sampling key.

Q: Is there a setting to allow updating key columns?
A: No. ClickHouse does not provide a setting to bypass this restriction. Key columns are fundamental to data organization, and updating them in place would corrupt the data layout.

Q: What about materialized columns or alias columns?
A: Materialized columns can be updated in some cases, but if they are part of a key expression, the same restriction applies. Alias columns are not stored physically and cannot be updated.

Q: Can I change the partition key value through a lightweight delete and re-insert?
A: Yes. You can DELETE FROM (or ALTER TABLE ... DELETE) the row with the old key value and INSERT a new row with the corrected key value. This effectively moves the data to the correct partition.