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

# CREATE TABLE


The `CREATE TABLE` call creates  [a table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main) with the specified data schema and key columns (`PRIMARY KEY`). It allows defining secondary indexes on the created table.




```yql
CREATE TABLE [IF NOT EXISTS] <table_name> (
  [<column_name> <column_data_type>] [FAMILY <family_name>] [NULL | NOT NULL] [DEFAULT <default_value>]
  [COMPRESSION([algorithm=<algorithm_name>[, level=<value>]])]
  [ENCODING([OFF|DICT])]
  [, ...],
    INDEX <index_name>
      [GLOBAL|LOCAL]
      [UNIQUE]
      [SYNC|ASYNC]
      [USING <index_type>]
      ON ( <index_columns> )
      [COVER ( <cover_columns> )]
      [WITH ( <parameter_name> = <parameter_value>[, ...])]
    [, ...]
  PRIMARY KEY ( <column>[, ...]),
  [FAMILY <column_family> ( family_options[, ...])]
)
[PARTITION BY HASH ( <column>[, ...])]
[WITH (<setting_name> = <setting_value>[, ...])]

[AS SELECT ...]
```



## Query parameters

### table_name

Path of the table being created.

When choosing a table name, follow the general [naming rules for schema objects](https://ydb.tech/docs/en/concepts/datamodel/cluster-namespace.md?version=main#object-naming-rules).

### IF NOT EXISTS

If a table with the specified name already exists, the statement execution is completely skipped — no checks or schema matching are performed, and no error occurs. Note that the existing table may differ in structure from the one you intended to create with this query — no comparison or equivalence check is performed.

### column_name

Name of the column being created in the new table.

When choosing a column name, follow the general [column naming rules](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-naming-rules).

### column_data_type

Data type of the column. The full 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 -->

### INDEX

Index definition on the table. Supported:

* [secondary indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/secondary_index.md?version=main),
* [vector indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/vector_index.md?version=main),
* [full-text indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/fulltext_index.md?version=main),
* [Bloom indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main),
* [min-max index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/min_max_index.md?version=main),
* [JSON indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/json_index.md?version=main).

### PRIMARY KEY

Defining the table's primary key. Specifies the columns that make up the primary key in the order listed. For more details on choosing a primary key, see the [Choosing a primary key](https://ydb.tech/docs/en/dev/primary-key/index.md?version=main) section.

### PARTITION BY HASH

Defining partitioning keys for **column-oriented** tables. Specifies the columns by whose hash the data [partitioning](https://ydb.tech/docs/en/concepts/glossary.md?version=main#partition) is performed. The columns must be part of the primary key. However, the columns do not necessarily have to be a prefix or suffix — the requirement is to be part of the primary key.

If the parameter is not specified, the table will be split into partitions by the same columns that are part of the primary key. For guidance on how to choose partitioning keys for column-oriented tables, see the article [Choosing keys for maximum column-oriented table performance](https://ydb.tech/docs/en/dev/primary-key/column-oriented.md?version=main).

For more details on partitioning column-oriented tables, see the [Partitioning Column-Oriented Tables](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#olap-tables-partitioning) section.

### FAMILY <column_family> (column group settings)

Defining a column group with specified parameters. For more details, see the [Column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main) section.

### WITH

Additional table creation parameters. For more details, see the [Additional parameters (WITH)](https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=main) section.

{% note info %}

YDB supports two types of tables:

* [Row-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#row-oriented-tables).
* [Column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables).

The table type when created is specified by the `STORE` parameter in the `WITH` block, where `ROW` means [row-oriented table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#row-oriented-tables) and `COLUMN` means [column-oriented table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables):


```yql
CREATE <table_name> (
  columns
  ...
)

WITH (
  STORE = COLUMN -- Default value ROW
)
```


By default, if the `STORE` parameter is not specified, a row-oriented table is created.

{% endnote %}

{% note info %}

When choosing a table name, follow the general [naming rules for schema objects](https://ydb.tech/docs/en/concepts/datamodel/cluster-namespace.md?version=main#object-naming-rules).

{% endnote %}

### AS SELECT

Creating and populating a table based on the results of the `SELECT` query. For more details, see the [Creating a table filled with query results](https://ydb.tech/docs/en/yql/reference/syntax/create_table/as_select.md?version=main) section.

## Table creation examples

{% list tabs %}

- Creating a row table


  ```yql
    CREATE TABLE <table_name> (
      a Uint64,
      b Uint64,
      c Float,
      PRIMARY KEY (a, b)
    );
  ```


  Example of creating a table using a default value (DEFAULT):


  ```yql
    CREATE TABLE table_with_default (
    id Uint64,
    name String DEFAULT "unknown",
    score Double NOT NULL DEFAULT 0.0,
    PRIMARY KEY (id)
  );
  ```




  For key columns, only [primitive](https://ydb.tech/docs/en/yql/reference/types/primitive.md?version=main) and [serial](https://ydb.tech/docs/en/yql/reference/types/serial.md?version=main) data types are allowed; for non-key columns, only [primitive](https://ydb.tech/docs/en/yql/reference/types/primitive.md?version=main) data types are allowed.




  Without additional modifiers, the column acquires an [optional type](https://ydb.tech/docs/en/yql/reference/types/optional.md?version=main) and allows `NULL` to be written as values. To obtain a non-optional type, use `NOT NULL`.



  It is mandatory to specify `PRIMARY KEY` with a non-empty list of columns. These columns become part of the key in the order they are listed.


  Example of creating a row table using partitioning options:


  ```yql
  CREATE TABLE <table_name> (
    a Uint64,
    b Uint64,
    c Float,
    PRIMARY KEY (a, b)
  )
  WITH (
    AUTO_PARTITIONING_BY_SIZE = ENABLED,
    AUTO_PARTITIONING_PARTITION_SIZE_MB = 512
  );
  ```


  This code will create a row table with automatic partitioning enabled by partition size (`AUTO_PARTITIONING_BY_SIZE`) and a preferred partition size (`AUTO_PARTITIONING_PARTITION_SIZE_MB`) of 512 megabytes. The full list of row table partitioning options is in the [Row table partitioning](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#partitioning_row_table) section of the [Table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main) article.

- Creating a column table

  ```yql
  CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
  )
  PARTITION BY HASH(b)
  WITH (
    STORE = COLUMN
  );
  ```


  For column tables, you can explicitly specify which columns will be used for partitioning using the `PARTITION BY HASH` construct. Typically, primary key columns with a large number of unique values are chosen for this, for example, `Timestamp`. If `PARTITION BY HASH` is not specified, partitioning will occur automatically across all columns that are part of the primary key. For more details on selecting and working with partitioning keys in column tables, see the [Choosing keys for maximum column-oriented table performance](https://ydb.tech/docs/en/dev/primary-key/column-oriented.md?version=main) article.

  Currently, column tables do not support automatic repartitioning, so it is important to specify the correct number of partitions when creating a table using the `AUTO_PARTITIONING_MIN_PARTITIONS_COUNT` parameter:


  ```yql
  CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
  )
  PARTITION BY HASH(b)
  WITH (
    STORE = COLUMN,
    AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 10
  );
  ```


  This code will create a column table with 10 partitions. For a full list of column table partitioning options, see the [Partitioning Column-Oriented Tables](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#olap-tables-partitioning) section of the [Table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main) article.

{% endlist %}



When creating row tables, you can specify:

* [Secondary index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/secondary_index.md?version=main).
* [Vector index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/vector_index.md?version=main).
* [Full-text index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/fulltext_index.md?version=main).
* [JSON index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/json_index.md?version=main).
* [Bloom index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main).
* [Column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main).
* [Additional parameters](https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=main).
* [Creating and populating a table based on query results](https://ydb.tech/docs/en/yql/reference/syntax/create_table/as_select.md?version=main).

When creating column tables, you can specify:

* [Bloom index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main).
* [Column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main).
* [Additional parameters](https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=main).
* [Creating and populating a table based on query results](https://ydb.tech/docs/en/yql/reference/syntax/create_table/as_select.md?version=main).