CREATE TABLE

The invocation of CREATE TABLE creates a table with the specified data schema and primary key columns (PRIMARY KEY). It also 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>]
  [, ...],
    INDEX <index_name>
      [GLOBAL]
      [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 ...]

Request parameters

table_name

The path of the table to be created.

When choosing a name for the table, consider the common schema object naming rules.

IF NOT EXISTS

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

column_name

The name of the column to be created in the new table.

When choosing a name for the column, consider the common 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 section.

FAMILY <family_name> (column setting)

Specifies that this column belongs to the specified column group. For more information, see Column groups.

DEFAULT <default_value>

Allows you to set a default value for a 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 column's data type.

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

NULL

This column can contain NULL values (default).

NOT NULL

This column does not accept NULL values.

INDEX

Definition of an index on the table. Secondary indexes and vector indexes are supported.

PRIMARY KEY

Definition of the primary key of the table. Specifies the columns that make up the primary key in the order of enumeration. For more information on selecting a primary key, see the Choosing a primary key article.

PARTITION BY HASH

Definition of the columns on which partitioning will occur for column-oriented tables. Specifies the columns on which partitioning will occur using the hash function. The columns must be part of the primary key. 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 partitioned on the same columns as those included in the primary key. For more information on selecting and working with partition keys in column-oriented tables, see the Choosing keys for maximum column-oriented table performance article.

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

FAMILY <column_family> (column group setting)

Definition of a column group with specified parameters. For more information, see the Column groups section.

WITH

Additional parameters for creating a table. For more information, see the Additional parameters (WITH) section.

YDB supports two types of tables:

• Row-oriented tables.
• Column-oriented tables.

The table type is specified by the STORE parameter in the WITH clause, where ROW indicates a row-oriented table and COLUMN indicates a column-oriented table:

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.

AS SELECT

Creating and filling a table with data from a SELECT query. For more information, see the Creating a table filled with query results section.

Examples of table creation

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-oriented tables, it is possible to specify:

• A secondary index.
• A vector index.
• Column groups.
• Additional parameters.
• Creating a table filled with query results.

When creating column-oriented tables, it is possible to specify:

• Column groups.
• Additional parameters.
• Creating a table filled with query results.