---
metadata:
  - name: generator
    content: Diplodoc Platform v5.50.6
alternate:
  - https://ydb.tech/docs/en/yql/reference/syntax/alter_table/columns.md?version=main
  - https://ydb.tech/docs/ru/yql/reference/syntax/alter_table/columns.md?version=main
sourcePath: en/core/yql/reference/syntax/alter_table/columns.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Changing columns

YDB supports adding columns to  row and column tables, deleting non-key columns from tables, and changing properties of existing columns.

## ADD COLUMN

Builds a new column with the specified name, type, and options for the specified table.


```yql
ALTER TABLE table_name ADD COLUMN column_name column_data_type [FAMILY <family_name>] [NULL | NOT NULL] [DEFAULT <default_value>] [COMPRESSION([algorithm=<algorithm_name>[, level=<value>]])] [ENCODING([OFF|DICT])];
```


## Request parameters

### table_name

The path of the table to which you want to add a new column.

### column_name

The name of the column to be created. When choosing a name for the column, consider the common [column naming rules](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-naming-rules).

### column_data_type

The data type of the column. The complete list of data types supported by YDB is available in the [YQL data types](https://ydb.tech/docs/en/yql/reference/types/index.md?version=main) section.

<!-- source: en/yql/reference/syntax/_includes/column_option_list.md -->
### FAMILY <family_name> (column setting)

Specifies the belonging of this column to the specified group of columns. For more details, see the section [Column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main).

### DEFAULT <default_value>

{% note warning %}

The `DEFAULT` option is supported:

* Only for [row-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#row-oriented-tables) tables.
* Only with literal values.

{% endnote %}

Allows you to set a default value for the column. If no value is specified for this column when inserting a row, the specified default value will be used. The default value must match the data type of the column.

The `DEFAULT false NOT NULL` construct is not allowed due to ambiguity of interpretation. In this case, you should use a comma-separated list or change the order of the options.

### NULL

This column can contain `NULL` values (by default).

### NOT NULL

This column does not accept `NULL` values.

### COMPRESSION([algorithm=<algorithm_name>[, level=<value>]])


<!-- source: en/_includes/only_allow_for_olap_note.md -->
{% note warning %}

<!-- source: en/_includes/only_allow_for_olap_text.md -->
Supported only for [column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables) tables.
<!-- endsource: en/_includes/only_allow_for_olap_text.md -->

{% endnote %}
<!-- endsource: en/_includes/only_allow_for_olap_note.md -->


The following compression parameters can be set for columns:

* `algorithm` — the data compression algorithm. Allowed values: `off` (disable compression), `lz4`, `zstd`.
* `level` — the compression level, supported only for the `zstd` algorithm (values from 0 to 22 are allowed).

If `COMPRESSION()` is specified without parameters, the default compression is used for the column. Currently, it is `lz4`; in future versions, it will be possible to configure the default compression at the cluster or table level.

### ENCODING([OFF|DICT])


<!-- source: en/_includes/only_allow_for_olap_note.md -->
{% note warning %}

<!-- source: en/_includes/only_allow_for_olap_text.md -->
Supported only for [column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables) tables.
<!-- endsource: en/_includes/only_allow_for_olap_text.md -->

{% endnote %}
<!-- endsource: en/_includes/only_allow_for_olap_note.md -->


Allows you to set the data encoding method for the column.

Available options:

* `ENCODING(DICT)` — enables dictionary encoding. Repeating values are replaced with small integer identifiers, and the values themselves are stored in a dictionary. Dictionary encoding is effective for columns with low cardinality (a small number of unique values). It reduces the amount of stored data and speeds up some operations. It is supported only for comparable data types, such as `String`, `Timestamp`, `UInt64`, and others. Using `ENCODING(DICT)` for incomparable types, such as `Json`, `JsonDocument`, or `Yson`, will result in an error.
* `ENCODING(OFF)` — disables special encoding. Data will be stored in the standard format without additional encoding.

If `ENCODING()` is set without parameters, the default encoding will be used for the column. Currently, it is `OFF`; in future versions, it will be possible to configure the default encoding at the database or table level.
<!-- endsource: en/yql/reference/syntax/_includes/column_option_list.md -->

## Example

The code below will add a column named `views` with data type `Uint64` to the `episodes` table.


```yql
ALTER TABLE episodes ADD COLUMN views Uint64;
```


The code below will add a column named `rate` with data type `Double` and default value `5.0` to the `episodes` table.


```yql
ALTER TABLE episodes ADD COLUMN rate Double NOT NULL DEFAULT 5.0;
ALTER TABLE episodes ADD COLUMN rate Double (DEFAULT 5.0, NOT NULL); -- alternative syntax
```


## ALTER COLUMN

Modifies properties of an existing column in the specified table. Property changes are applied without recreating the column. Some properties apply only to newly written data or during compaction (see the description of each property for details).


```yql
ALTER TABLE table_name ALTER COLUMN column_name SET [FAMILY <family_name>] [NOT NULL] [DEFAULT <default_value>] [COMPRESSION([algorithm=<algorithm_name>[, level=<value>]])] [ENCODING([OFF|DICT])];
ALTER TABLE table_name ALTER COLUMN column_name DROP [FAMILY] [NOT NULL] [DEFAULT] [COMPRESSION] [ENCODING];
```


### Request parameters

#### table_name

The path of the table containing the column to change.

#### column_name

The name of the column to change in the specified table.

#### SET

Set a column property.

#### DROP

Remove a column property.

<!-- source: en/yql/reference/syntax/_includes/column_option_list_alter.md -->
### FAMILY <family_name> (column setting)

Specifies that this column belongs to the specified column group. For more information, see [Column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main).

### DEFAULT <default_value>

{% note warning %}

The `DEFAULT` option is supported:

* Only for [row](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#row-oriented-tables) tables.
* Only with literal values.

{% endnote %}

Sets a default value for the column. If no value is specified for this column when inserting a row, the specified default value is used. The default value must match the column's data type.

### NOT NULL

There are two operations:

* `SET NOT NULL` — sets the `NOT NULL` constraint for the column.
* `DROP NOT NULL` — removes the `NOT NULL` constraint from the column, again allowing `NULL` values.

For more information about how these operations work, see [here](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/not_null.md?version=main).

### COMPRESSION([algorithm=<algorithm_name>[, level=<value>]])


<!-- source: en/_includes/only_allow_for_olap_note.md -->
{% note warning %}

<!-- source: en/_includes/only_allow_for_olap_text.md -->
Supported only for [column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables) tables.
<!-- endsource: en/_includes/only_allow_for_olap_text.md -->

{% endnote %}
<!-- endsource: en/_includes/only_allow_for_olap_note.md -->


The following compression parameters can be set for columns:

* `algorithm` — data compression algorithm. Valid values: `off` (disable compression), `lz4`, `zstd`.
* `level` — compression level, supported only for the `zstd` algorithm (valid values from 0 to 22).

If `COMPRESSION()` is specified without parameters, the default compression is used for the column. Currently this is `lz4`; future versions will allow configuring default compression at the cluster or table level.

### ENCODING([OFF|DICT])


<!-- source: en/_includes/only_allow_for_olap_note.md -->
{% note warning %}

<!-- source: en/_includes/only_allow_for_olap_text.md -->
Supported only for [column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables) tables.
<!-- endsource: en/_includes/only_allow_for_olap_text.md -->

{% endnote %}
<!-- endsource: en/_includes/only_allow_for_olap_note.md -->


Sets the encoding method for the column data.

Available options:

* `ENCODING(DICT)`: enables dictionary encoding. Duplicate values are replaced with small integer identifiers, and the values themselves are stored in a dictionary. Dictionary encoding is effective for columns with low cardinality (a small number of unique values). It reduces the amount of stored data and speeds up some operations. Supported only for comparable data types, such as `String`, `Timestamp`, `UInt64`, and others. For non-comparable types, such as `Json`, `JsonDocument`, or `Yson`, using `ENCODING(DICT)` will result in an error.
* `ENCODING(OFF)`: disables special encoding. Data will be stored in standard format without additional encoding.

If `ENCODING()` is specified without parameters, the default encoding will be used for the column. Currently it is `OFF`; future versions will allow configuring the default encoding at the database or table level.
<!-- endsource: en/yql/reference/syntax/_includes/column_option_list_alter.md -->

A single `ALTER TABLE` statement can specify multiple `ALTER COLUMN` actions separated by commas.

### Examples

The code below sets a default value for the `rate` column of the `episodes` table.


```yql
ALTER TABLE episodes ALTER COLUMN rate SET DEFAULT 5.0;
```


The code below changes default values for several columns of a table in a single statement — setting new defaults for `col_1` and `col_2` and clearing the default for `col_3`.


```yql
ALTER TABLE default_columns
    ALTER COLUMN col_1 SET DEFAULT "new_a"u,
    ALTER COLUMN col_2 SET DEFAULT 99,
    ALTER COLUMN col_3 DROP DEFAULT;
```


Reset column compression settings:


```yql
ALTER TABLE compressed_table ALTER COLUMN info SET COMPRESSION();
```


After the query runs, the column uses the default compression algorithm again (see the `COMPRESSION` option above).

Enable dictionary encoding on a column:


```yql
ALTER TABLE movies ALTER COLUMN genre SET ENCODING(DICT);
```


## DROP COLUMN

Deletes a column with the specified name from the specified table.


```yql
ALTER TABLE table_name DROP COLUMN column_name;
```


### Request parameters

#### table_name

The path of the table from which you want to delete a column.

#### column_name

The name of the column to be deleted.

### Example

The code below will delete the column named `views` from the `episodes` table.


```yql
ALTER TABLE episodes DROP COLUMN views;
```