Help Center/CloudTable Service/User Guide/Using ClickHouse/Common SQL Commands for Storage-Compute Coupled ClickHouse/ALTER TABLE
Updated on 2025-12-29 GMT+08:00
View PDF

ALTER TABLE

This section describes the basic syntax and usage of the SQL statement for modifying a table structure in ClickHouse.

Basic Syntax

ALTER TABLE [database_name].name [ON CLUSTER default_cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

ALTER supports only *MergeTree, Merge, and Distributed engine tables.

Example:

  1. Create a table named DB_table1. 
    CREATE TABLE DB_table1 ON CLUSTER default_cluster(Year UInt16,Quarter UInt8,Month UInt8,DayofMonth UInt8,DayOfWeek UInt8,FlightDate Date,FlightNum String,Div5WheelsOff String,Div5TailNum String)ENGINE = MergeTree() PARTITION BY toYYYYMM(FlightDate) PRIMARY KEY (intHash32(FlightDate)) ORDER BY (intHash32(FlightDate),FlightNum) SAMPLE BY intHash32(FlightDate) SETTINGS index_granularity= 8192;
  2. Add the test column to table DB_table1. 
    ALTER TABLE DB_table1 ADD COLUMN test String DEFAULT 'defaultvalue';

    Query the table.

    desc DB_tables;
  3. Change the type of the Year column in the DB_table1 table to UInt8. 
    ALTER TABLE DB_table1 MODIFY COLUMN Year UInt8;

    View the table structure.

    desc DB_tables;
  4. Delete the test column from the DB_table1 table. 
    ALTER TABLE DB_table1 DROP COLUMN test;

    Query the table.

    desc DB_tables;
  5. Change the name of the Month column in the DB_table1 table to Month_test. 
    ALTER TABLE DB_table1 RENAME COLUMN Month to Month_test;

    Query the table.

    desc DB_tables;