Help Center/ CloudTable Service/ User Guide/ Using Doris/ Common SQL Commands of Doris/ Modifying a Table Structure
Updated on 2025-07-24 GMT+08:00
View PDF
Share

Modifying a Table Structure

There are different methods for modifying table structures in an aggregate model and a non-aggregate model. The methods for modifying the key and value columns are also different. Specifically:

  • If AGGREGATE KEY is specified during table creation, the table uses an aggregate model. In other scenarios, a non-aggregate model is used.
  • In the table creation statement, the columns following keyword 'unique key', 'aggregate key', or 'duplicate key' are the key columns, and the remaining columns are value columns.

Example for an Aggregate Model

The aggregate type of the aggregate columns cannot be changed.

  • Add the new_col column (key column) after the col1 column. 
    ALTER TABLE example_db.my_table  ADD COLUMN new_col INT DEFAULT "0" AFTER col1;
  • Add the new_col column (value column with the SUM aggregate type) after the col1 column. 
    ALTER TABLE example_db.my_table ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1;
  • Change the type of the col1 column (key column) to BIGINT. 
    ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT DEFAULT "1";
  • Change the type of the col1 column (value column) to BIGINT. 
    ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT MAX DEFAULT "1";
  • Delete the col1 column. 
    ALTER TABLE example_db.my_table DROP COLUMN col1;

Example for a Non-Aggregate Model

  • Add the new_col column (key column) after the col1 column. 
    ALTER TABLE example_db.my_table ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1;
  • Add the new_col column (value column) after the col1 column. 
    ALTER TABLE example_db.my_table ADD COLUMN new_col INT DEFAULT "0" AFTER col1;
  • Change the type of the col1 column (key column) to BIGINT. 
    ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT KEY DEFAULT "1";
  • Change the type of the col1 column (value column) to BIGINT. 
    ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT DEFAULT "1";
  • Delete the col1 column. 
    ALTER TABLE example_db.my_table DROP COLUMN col1;