Skip to main content
Skip to main content
Edit this page

Column Manipulations

A set of queries that allow changing the table structure.

Syntax:

In the query, specify a list of one or more comma-separated actions. Each action is an operation on a column.

The following actions are supported:

ADD COLUMN​

Adds a new column to the table with the specified name, type, codec and default_expr (see the section Default expressions).

If the IF NOT EXISTS clause is included, the query won't return an error if the column already exists. If you specify AFTER name_after (the name of another column), the column is added after the specified one in the list of table columns. If you want to add a column to the beginning of the table use the FIRST clause. Otherwise, the column is added to the end of the table. For a chain of actions, name_after can be the name of a column that is added in one of the previous actions.

Adding a column just changes the table structure, without performing any actions with data. The data does not appear on the disk after ALTER. If the data is missing for a column when reading from the table, it is filled in with default values (by performing the default expression if there is one, or using zeros or empty strings). The column appears on the disk after merging data parts (see MergeTree).

This approach allows us to complete the ALTER query instantly, without increasing the volume of old data.

Example:

DROP COLUMN​

Deletes the column with the name name. If the IF EXISTS clause is specified, the query won't return an error if the column does not exist.

Deletes data from the file system. Since this deletes entire files, the query is completed almost instantly.

Tip

You can't delete a column if it is referenced by materialized view. Otherwise, it returns an error.

Example:

RENAME COLUMN​

Renames the column name to new_name. If the IF EXISTS clause is specified, the query won't return an error if the column does not exist. Since renaming does not involve the underlying data, the query is completed almost instantly.

NOTE: Columns specified in the key expression of the table (either with ORDER BY or PRIMARY KEY) cannot be renamed. Trying to change these columns will produce SQL Error [524].

Example:

CLEAR COLUMN​

Resets all data in a column for a specified partition. Read more about setting the partition name in the section How to set the partition expression.

If the IF EXISTS clause is specified, the query won't return an error if the column does not exist.

Example:

COMMENT COLUMN​

Adds a comment to the column. If the IF EXISTS clause is specified, the query won't return an error if the column does not exist.

Each column can have one comment. If a comment already exists for the column, a new comment overwrites the previous comment.

Comments are stored in the comment_expression column returned by the DESCRIBE TABLE query.

Example:

MODIFY COLUMN​

This query changes the name column properties:

  • Type

  • Default expression

  • Compression Codec

  • TTL

  • Column-level Settings

For examples of columns compression CODECS modifying, see Column Compression Codecs.

For examples of columns TTL modifying, see Column TTL.

For examples of column-level settings modifying, see Column-level Settings.

If the IF EXISTS clause is specified, the query won't return an error if the column does not exist.

When changing the type, values are converted as if the toType functions were applied to them. If only the default expression is changed, the query does not do anything complex, and is completed almost instantly.

Example:

Changing the column type is the only complex action – it changes the contents of files with data. For large tables, this may take a long time.

The query also can change the order of the columns using FIRST | AFTER clause, see ADD COLUMN description, but column type is mandatory in this case.

Example:

The ALTER query is atomic. For MergeTree tables it is also lock-free.

The ALTER query for changing columns is replicated. The instructions are saved in ZooKeeper, then each replica applies them. All ALTER queries are run in the same order. The query waits for the appropriate actions to be completed on the other replicas. However, a query to change columns in a replicated table can be interrupted, and all actions will be performed asynchronously.

MODIFY COLUMN REMOVE​

Removes one of the column properties: DEFAULT, ALIAS, MATERIALIZED, CODEC, COMMENT, TTL, SETTINGS.

Syntax:

Example

Remove TTL:

See Also

MODIFY COLUMN MODIFY SETTING​

Modify a column setting.

Syntax:

Example

Modify column's max_compress_block_size to 1MB:

MODIFY COLUMN RESET SETTING​

Reset a column setting, also removes the setting declaration in the column expression of the table's CREATE query.

Syntax:

Example

Reset column setting max_compress_block_size to it's default value:

MATERIALIZE COLUMN​

Materializes a column with a DEFAULT or MATERIALIZED value expression. When adding a materialized column using ALTER TABLE table_name ADD COLUMN column_name MATERIALIZED, existing rows without materialized values are not automatically filled. MATERIALIZE COLUMN statement can be used to rewrite existing column data after a DEFAULT or MATERIALIZED expression has been added or updated (which only updates the metadata but does not change existing data). Note that materializing a column in the sort key is an invalid operation because it could break the sort order. Implemented as a mutation.

For columns with a new or updated MATERIALIZED value expression, all existing rows are rewritten.

For columns with a new or updated DEFAULT value expression, the behavior depends on the ClickHouse version:

  • In ClickHouse < v24.2, all existing rows are rewritten.
  • ClickHouse >= v24.2 distinguishes if a row value in a column with DEFAULT value expression was explicitly specified when it was inserted, or not, i.e. calculated from the DEFAULT value expression. If the value was explicitly specified, ClickHouse keeps it as is. If the value was calculated, ClickHouse changes it to the new or updated MATERIALIZED value expression.

Syntax:

  • If you specify a PARTITION, a column will be materialized with only the specified partition.

Example

See Also

Limitations​

The ALTER query lets you create and delete separate elements (columns) in nested data structures, but not whole nested data structures. To add a nested data structure, you can add columns with a name like name.nested_name and the type Array(T). A nested data structure is equivalent to multiple array columns with a name that has the same prefix before the dot.

There is no support for deleting columns in the primary key or the sampling key (columns that are used in the ENGINE expression). Changing the type for columns that are included in the primary key is only possible if this change does not cause the data to be modified (for example, you are allowed to add values to an Enum or to change a type from DateTime to UInt32).

If the ALTER query is not sufficient to make the table changes you need, you can create a new table, copy the data to it using the INSERT SELECT query, then switch the tables using the RENAME query and delete the old table.

The ALTER query blocks all reads and writes for the table. In other words, if a long SELECT is running at the time of the ALTER query, the ALTER query will wait for it to complete. At the same time, all new queries to the same table will wait while this ALTER is running.

For tables that do not store data themselves (such as Merge and Distributed), ALTER just changes the table structure, and does not change the structure of subordinate tables. For example, when running ALTER for a Distributed table, you will also need to run ALTER for the tables on all remote servers.