> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Table

# CREATE TABLE

Creates a new table. By default, tables are created only on the current server.
Distributed DDL queries are implemented as `ON CLUSTER` clause, which is [described separately](/reference/statements/distributed-ddl).

<h2 id="syntax-forms">
  Syntax forms
</h2>

This query can have various syntax forms depending on the use case.

<h3 id="with-explicit-schema">
  Create a table with an explicit schema
</h3>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [COMMENT 'comment for column'] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [COMMENT 'comment for column'] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine
  [COMMENT 'comment for table']
```

Creates a table named `table_name` in the `db` database or the current database if `db` is not set, with the structure specified in brackets and the `engine` engine.
The structure of the table is a list of column descriptions, secondary indexes, projections and constraints . If [primary key](#primary-key) is supported by the engine, it will be indicated as parameter for the table engine.

A column description is `name type` in the simplest case. Example: `RegionID UInt32`.

Expressions can also be defined for default values (see below).

If necessary, primary key can be specified, with one or more key expressions.

Comments can be added for columns and for the table.

<h3 id="with-a-schema-similar-to-other-table">
  Create a table with an existing tables schema
</h3>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db2.]table_clone AS [db.]table [ENGINE = engine]
```

ClickHouse supports the ability to copy the schema and data of an existing table.

For replicating the schema of an existing table:

This creates a table with the same structure as another table.

<h3 id="with-a-schema-and-data-cloned-from-another-table">
  Create a table with an existing tables schema and data
</h3>

For replicating the schema and data of an existing table:

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db2.]table_clone CLONE AS [db.]table [ENGINE = engine]
```

This creates a table with the same schema and data as an existing table.  After the new table is created, all partitions from `db.table` are attached to it. In other words, the data of `db.table` is cloned into `db2.table_clone` upon creation. This query is equivalent to the following:

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db2.]table_clone AS [db.]table [ENGINE = engine];
ALTER TABLE [db2.]table_clone ATTACH PARTITION ALL FROM [db.]table;
```

For both features, you can specify a different engine for the table. If the engine is not specified, the same engine will be used as for the original table (`db.table`).

<h3 id="from-a-table-function">
  Create a table with a table function
</h3>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()
```

Creates a table with the same result as that of the [table function](/reference/functions/table-functions/index) specified. The created table will also work in the same way as the corresponding table function that was specified.

<h3 id="from-select-query">
  Create a table with a SELECT query
</h3>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...
```

Creates a table with a structure like the result of the `SELECT` query, with the `engine` engine, and fills it with data from `SELECT`. Also you can explicitly specify columns description.

If the table already exists and `IF NOT EXISTS` is specified, the query won't do anything.

There can be other clauses after the `ENGINE` clause in the query. See detailed documentation on how to create tables in the descriptions of [table engines](/reference/engines/table-engines/index).

**Example**

```sql title="Query" theme={null}
CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;
SELECT x, toTypeName(x) FROM t1;
```

```text title="Response" theme={null}
┌─x─┬─toTypeName(x)─┐
│ 1 │ String        │
└───┴───────────────┘
```

<h2 id="default_values">
  Specify column default values
</h2>

The column description can specify a default value expression in the form of `DEFAULT expr`, `MATERIALIZED expr`, or `ALIAS expr`. Example: `URLDomain String DEFAULT domain(URL)`.

The expression `expr` is optional. If it is omitted, the column type must be specified explicitly and the default value will be `0` for numeric columns, `''` (the empty string) for string columns, `[]` (the empty array) for array columns, `1970-01-01` for date columns, or `NULL` for nullable columns.

The column type of a default value column can be omitted in which case it is inferred from `expr`'s type. For example the type of column `EventDate DEFAULT toDate(EventTime)` will be date.

If both a data type and a default value expression are specified, an implicit type casting function inserted which converts the expression to the specified type. Example: `Hits UInt32 DEFAULT 0` is internally represented as `Hits UInt32 DEFAULT toUInt32(0)`.

A default value expression `expr` may reference arbitrary table columns and constants. ClickHouse checks that changes of the table structure do not introduce loops in the expression calculation. For INSERT, it checks that expressions are resolvable – that all columns they can be calculated from have been passed.

<h3 id="default">
  DEFAULT
</h3>

`DEFAULT expr`

Normal default value. If the value of such a column is not specified in an INSERT query, it is computed from `expr`.

Example:

```sql theme={null}
CREATE OR REPLACE TABLE test
(
    id UInt64,
    updated_at DateTime DEFAULT now(),
    updated_at_date Date DEFAULT toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test (id) VALUES (1);

SELECT * FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:06:46 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘
```

<h3 id="materialized">
  MATERIALIZED
</h3>

`MATERIALIZED expr`

Materialized expression. Values of such columns are automatically calculated according to the specified materialized expression when rows are inserted. Values cannot be explicitly specified during `INSERT`s.

Also, default value columns of this type are not included in the result of `SELECT *`. This is to preserve the invariant that the result of a `SELECT *` can always be inserted back into the table using `INSERT`. This behavior can be disabled with setting `asterisk_include_materialized_columns`.

Example:

```sql theme={null}
CREATE OR REPLACE TABLE test
(
    id UInt64,
    updated_at DateTime MATERIALIZED now(),
    updated_at_date Date MATERIALIZED toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1);

SELECT * FROM test;
┌─id─┐
│  1 │
└────┘

SELECT id, updated_at, updated_at_date FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:08:08 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

SELECT * FROM test SETTINGS asterisk_include_materialized_columns=1;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:08:08 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘
```

<h3 id="ephemeral">
  EPHEMERAL
</h3>

`EPHEMERAL [expr]`

Ephemeral column. Columns of this type are not stored in the table and it is not possible to SELECT from them. The only purpose of ephemeral columns is to build default value expressions of other columns from them.

An insert without explicitly specified columns will skip columns of this type. This is to preserve the invariant that the result of a `SELECT *` can always be inserted back into the table using `INSERT`.

Example:

```sql theme={null}
CREATE OR REPLACE TABLE test
(
    id UInt64,
    unhexed String EPHEMERAL,
    hexed FixedString(4) DEFAULT unhex(unhexed)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test (id, unhexed) VALUES (1, '5a90b714');

SELECT
    id,
    hexed,
    hex(hexed)
FROM test
FORMAT Vertical;

Row 1:
──────
id:         1
hexed:      Z��
hex(hexed): 5A90B714
```

<h3 id="alias">
  ALIAS
</h3>

`ALIAS expr`

Calculated columns (synonym). Column of this type are not stored in the table and it is not possible to INSERT values into them.

When SELECT queries explicitly reference columns of this type, the value is computed at query time from `expr`. By default, `SELECT *` excludes ALIAS columns. This behavior can be disabled with setting `asterisk_include_alias_columns`.

When using the ALTER query to add new columns, old data for these columns is not written. Instead, when reading old data that does not have values for the new columns, expressions are computed on the fly by default. However, if running the expressions requires different columns that are not indicated in the query, these columns will additionally be read, but only for the blocks of data that need it.

If you add a new column to a table but later change its default expression, the values used for old data will change (for data where values were not stored on the disk). Note that when running background merges, data for columns that are missing in one of the merging parts is written to the merged part.

It is not possible to set default values for elements in nested data structures.

```sql theme={null}
CREATE OR REPLACE TABLE test
(
    id UInt64,
    size_bytes Int64,
    size String ALIAS formatReadableSize(size_bytes)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1, 4678899);

SELECT id, size_bytes, size FROM test;
┌─id─┬─size_bytes─┬─size─────┐
│  1 │    4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

SELECT * FROM test SETTINGS asterisk_include_alias_columns=1;
┌─id─┬─size_bytes─┬─size─────┐
│  1 │    4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘
```

<h2 id="null-or-not-null-modifiers">
  Use NULL or NOT NULL modifiers
</h2>

`NULL` and `NOT NULL` modifiers after data type in column definition allow or do not allow it to be [Nullable](/reference/data-types/nullable).

If the type is not `Nullable` and if `NULL` is specified, it will be treated as `Nullable`; if `NOT NULL` is specified, then no. For example, `INT NULL` is the same as `Nullable(INT)`. If the type is `Nullable` and `NULL` or `NOT NULL` modifiers are specified, the exception will be thrown.

See also [data\_type\_default\_nullable](/reference/settings/session-settings/other#data_type_default_nullable) setting.

<h2 id="primary-key">
  Primary key
</h2>

You can define a [primary key](/reference/engines/table-engines/mergetree-family/mergetree#primary-keys-and-indexes-in-queries) when creating a table. A primary key can be specified in two ways:

<Columns cols={2}>
  <div>
    **Inside the column list**

    ```sql theme={null}
    CREATE TABLE [db.]table_name
    (
        name1 type1, name2 type2, ...,
        PRIMARY KEY(expr1[, expr2,...])
    )
    ENGINE = engine;
    ```
  </div>

  <div>
    **Outside the column list**

    ```sql theme={null}
    CREATE TABLE [db.]table_name
    (
        name1 type1, name2 type2, ...
    )
    ENGINE = engine
    PRIMARY KEY(expr1[, expr2,...]);
    ```
  </div>
</Columns>

<Tip>
  You can't combine both ways in one query.
</Tip>

<h2 id="constraints">
  Specify table constraints
</h2>

Along with columns descriptions, constraints could be defined:

<h3 id="constraint">
  CONSTRAINT
</h3>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine
```

`boolean_expr_1` could by any boolean expression. If constraints are defined for the table, each of them will be checked for every row in `INSERT` query. If any constraint is not satisfied — server will raise an exception with constraint name and checking expression.

Adding large amount of constraints can negatively affect performance of big `INSERT` queries.

Existing constraints across all tables can be inspected via the [`system.constraints`](/reference/system-tables/constraints) table.

<h3 id="assume">
  ASSUME
</h3>

The `ASSUME` clause is used to define a `CONSTRAINT` on a table that is assumed to be true. This constraint can then be used by the optimizer to enhance the performance of SQL queries.

Take this example where `ASSUME CONSTRAINT` is used in the creation of the `users_a` table:

```sql theme={null}
CREATE TABLE users_a (
    uid Int16, 
    name String, 
    age Int16, 
    name_len UInt8 MATERIALIZED length(name), 
    CONSTRAINT c1 ASSUME length(name) = name_len
) 
ENGINE=MergeTree 
ORDER BY (name_len, name);
```

Here, `ASSUME CONSTRAINT` is used to assert that the `length(name)` function always equals the value of the `name_len` column. This means that whenever `length(name)` is called in a query, ClickHouse can replace it with `name_len`, which should be faster because it avoids calling the `length()` function.

Then, when executing the query `SELECT name FROM users_a WHERE length(name) < 5;`, ClickHouse can optimize it to `SELECT name FROM users_a WHERE name_len < 5`; because of the `ASSUME CONSTRAINT`. This can make the query run faster because it avoids calculating the length of `name` for each row.

`ASSUME CONSTRAINT` **does not enforce the constraint**, it merely informs the optimizer that the constraint holds true. If the constraint is not actually true, the results of the queries may be incorrect. Therefore, you should only use `ASSUME CONSTRAINT` if you are sure that the constraint is true.

<h2 id="ttl-expression">
  Define storage time with TTL
</h2>

Defines storage time for values. Can be specified only for MergeTree-family tables. For the detailed description, see [TTL for columns and tables](/reference/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl).

<h2 id="column_compression_codec">
  Select column compression codecs
</h2>

<a id="general-purpose-codecs" />

<a id="none" />

<a id="lz4" />

<a id="lz4hc" />

<a id="zstd" />

<a id="zxc" />

<a id="zstd_qat" />

<a id="deflate_qpl" />

<a id="specialized-codecs" />

<a id="delta" />

<a id="doubledelta" />

<a id="gcd" />

<a id="gorilla" />

<a id="alp" />

<a id="fpc" />

<a id="sz3" />

<a id="t64" />

<a id="quantized" />

<a id="encryption-codecs" />

<a id="aes_128_gcm_siv" />

<a id="aes-256-gcm-siv" />

<a id="adaptive-codec-selection" />

By default, ClickHouse applies `lz4` compression in the self-managed version, and `zstd` in ClickHouse Cloud. You can also define the compression method for each individual column in the `CREATE TABLE` query:

```sql theme={null}
CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...
```

For the available general purpose, specialized and encryption codecs, see [Column compression codecs](/reference/statements/create/table/codec).

<h2 id="temporary-tables">
  Create temporary tables
</h2>

ClickHouse supports temporary tables, which disappear when the session ends. For details, see [CREATE TEMPORARY TABLE](/reference/statements/create/table/temporary-table).

<h2 id="replace-table">
  Update a table atomically with REPLACE TABLE
</h2>

<a id="syntax" />

<a id="examples" />

The `REPLACE` statement allows you to update a table [atomically](/concepts/core-concepts/glossary#atomicity). For details, see [REPLACE TABLE](/reference/statements/create/table/replace-table).

<h2 id="comment-clause">
  Add a table comment
</h2>

You can add a comment to the table when creating it.

**Syntax**

```sql theme={null}
CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
COMMENT 'Comment'
```

<Note>
  The `COMMENT` clause must be specified **after** any storage-specific clauses such as `PARTITION BY`, `ORDER BY`, and storage-specific `SETTINGS`.

  After the `COMMENT` clause, only query-specific `SETTINGS` (like `max_threads`, etc.) will be parsed, not storage-related settings.

  This means the correct clause order is:

  * `ENGINE`
  * storage clauses
  * `COMMENT`
  * query settings (if any)
</Note>

**Example**

```sql title="Query" theme={null}
CREATE TABLE t1 (x String) ENGINE = Memory COMMENT 'The temporary table';
SELECT name, comment FROM system.tables WHERE name = 't1';
```

```text title="Response" theme={null}
┌─name─┬─comment─────────────┐
│ t1   │ The temporary table │
└──────┴─────────────────────┘
```

<h2 id="related-content">
  Related content
</h2>

* Blog: [Optimizing ClickHouse with Schemas and Codecs](https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema)
* Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse)