CREATE TABLE

The CREATE TABLE call creates a table with the specified data schema and key columns (PRIMARY KEY). It allows defining secondary indexes on the created table.

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.

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.

column_data_type

Data type of the column. The full list of data types supported by YDB is available in the YQL data types section.

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.

DEFAULT <default_value>

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=]])

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])

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.

INDEX

Index definition on the table. Supported:

• secondary indexes,
• vector indexes,
• full-text indexes,
• Bloom indexes,
• min-max index,
• JSON indexes.

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 section.

PARTITION BY HASH

Defining partitioning keys for column-oriented tables. Specifies the columns by whose hash the data partitioning 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.

For more details on partitioning column-oriented tables, see the Partitioning Column-Oriented Tables section.

FAMILY <column_family> (column group settings)

Defining a column group with specified parameters. For more details, see the Column groups section.

WITH

Additional table creation parameters. For more details, see the Additional parameters (WITH) section.

CREATE <table_name> (
  columns
  ...
)

WITH (
  STORE = COLUMN -- Default value ROW
)

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 section.

Table creation examples

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

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

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
);

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
);

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
);

When creating row tables, you can specify:

• Secondary index.
• Vector index.
• Full-text index.
• JSON index.
• Bloom index.
• Column groups.
• Additional parameters.
• Creating and populating a table based on query results.

When creating column tables, you can specify:

• Bloom index.
• Column groups.
• Additional parameters.
• Creating and populating a table based on query results.