How to Change Column Type in ClickHouse

How do I change a column type in ClickHouse?

ALTER TABLE ... MODIFY COLUMN lets you change a column’s data type in ClickHouse while preserving data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I change a column type in ClickHouse?

Run ALTER TABLE table_name MODIFY COLUMN col_name new_type. ClickHouse rewrites data in the background, so the table stays queryable.

What is the exact syntax?

Syntax appears below. You can also reposition the column with AFTER and change default or codec settings in one statement.

Can I modify columns in production safely?

Yes. ClickHouse performs the mutation asynchronously.Monitor system.mutations to track progress and avoid heavy loads during peak traffic.

When should I use FINAL?

Use FINAL only when immediately querying the table after the mutation and you require fully merged data. Otherwise, let background merges finish naturally.

How do I monitor the mutation?

Select * from system.mutations where table = 'Orders' to see status, progress, and possible failures.

Practical example with ecommerce data

Suppose you stored price as Float32 but need better precision.ALTER TABLE Products MODIFY COLUMN price Decimal(18,2) updates the type without dropping data.

Best practices

1) Schedule large mutations during low-traffic windows. 2) Always test on a staging replica. 3) Backup data or verify replication health before execution.

Why How to Change Column Type in ClickHouse is important

How to Change Column Type in ClickHouse Example Usage


-- Convert total_amount in Orders from Float32 to Decimal for accuracy
ALTER TABLE Orders
    MODIFY COLUMN total_amount Decimal(18,2);

-- Verify mutation progress
SELECT database, table, mutation_id, is_done, progress
FROM system.mutations
WHERE table = 'Orders';

How to Change Column Type in ClickHouse Syntax


ALTER TABLE [db.]table_name
    MODIFY COLUMN [IF EXISTS] column_name new_data_type
    [DEFAULT expr] [CODEC codec] [AFTER other_column];

-- Example
ALTER TABLE Products
    MODIFY COLUMN price Decimal(18,2) AFTER name;

Options:
IF EXISTS – skip error if the column is missing.
DEFAULT – redefine default expression.
CODEC – set compression codec.
AFTER – reposition column in the order list.

Common Mistakes

Omitting the scale on Decimal, e.g., Decimal(18) instead of Decimal(18,2). ClickHouse treats it as Decimal(18,0), causing rounding. Always specify both precision and scale.
Running multiple large MODIFY COLUMN commands separately. Each triggers its own mutation. Combine them in one ALTER TABLE to reduce background load.