CREATE TABLE

`DoubleDelta` and `Gorilla` codecs are used in Gorilla TSDB as the components of its compressing algorithm. Gorilla approach is effective in scenarios when there is a sequence of slowly changing values with their timestamps. Timestamps are effectively compressed by the `DoubleDelta` codec, and values are effectively compressed by the `Gorilla` codec. For example, to get an effectively stored table, you can create it in the following configuration:

```
CREATE TABLE codec_example
(
    timestamp DateTime CODEC(DoubleDelta),
    slow_values Float32 CODEC(Gorilla)
)
ENGINE = MergeTree()
```

### Encryption Codecs[​](#encryption-codecs "Direct link to Encryption Codecs")

These codecs don't actually compress data, but instead encrypt data on disk. These are only available when an encryption key is specified by [encryption](/docs/operations/server-configuration-parameters/settings#encryption) settings. Note that encryption only makes sense at the end of codec pipelines, because encrypted data usually can't be compressed in any meaningful way.

Encryption codecs:

#### AES\_128\_GCM\_SIV[​](#aes_128_gcm_siv "Direct link to AES_128_GCM_SIV")

`CODEC('AES-128-GCM-SIV')` — Encrypts data with AES-128 in [RFC 8452](https://tools.ietf.org/html/rfc8452) GCM-SIV mode.

#### AES-256-GCM-SIV[​](#aes-256-gcm-siv "Direct link to AES-256-GCM-SIV")

`CODEC('AES-256-GCM-SIV')` — Encrypts data with AES-256 in GCM-SIV mode.

These codecs use a fixed nonce and encryption is therefore deterministic. This makes it compatible with deduplicating engines such as [ReplicatedMergeTree](/docs/engines/table-engines/mergetree-family/replication) but has a weakness: when the same data block is encrypted twice, the resulting ciphertext will be exactly the same so an adversary who can read the disk can see this equivalence (although only the equivalence, without getting its content).

Note

Most engines including the "\*MergeTree" family create index files on disk without applying codecs. This means plaintext will appear on disk if an encrypted column is indexed.

Note

If you perform a SELECT query mentioning a specific value in an encrypted column (such as in its WHERE clause), the value may appear in [system.query\_log](/docs/operations/system-tables/query_log). You may want to disable the logging.

**Example**

```
CREATE TABLE mytable
(
    x String CODEC(AES_128_GCM_SIV)
)
ENGINE = MergeTree ORDER BY x;
```

Note

If compression needs to be applied, it must be explicitly specified. Otherwise, only encryption will be applied to data.

**Example**

```
CREATE TABLE mytable
(
    x String CODEC(Delta, LZ4, AES_128_GCM_SIV)
)
ENGINE = MergeTree ORDER BY x;
```

## Temporary Tables[​](#temporary-tables "Direct link to Temporary Tables")

Note

Please note that temporary tables are not replicated. As a result, there is no guarantee that data inserted into a temporary table will be available in other replicas. The primary use case where temporary tables can be useful is for querying or joining small external datasets during a single session.

ClickHouse supports temporary tables which have the following characteristics:

- Temporary tables disappear when the session ends, including if the connection is lost.
- A temporary table uses the Memory table engine when engine is not specified and it may use any table engine except Replicated and `KeeperMap` engines.
- The DB can't be specified for a temporary table. It is created outside of databases.
- Impossible to create a temporary table with distributed DDL query on all cluster servers (by using `ON CLUSTER`): this table exists only in the current session.
- If a temporary table has the same name as another one and a query specifies the table name without specifying the DB, the temporary table will be used.
- For distributed query processing, temporary tables with Memory engine used in a query are passed to remote servers.

To create a temporary table, use the following syntax:

```
CREATE [OR REPLACE] TEMPORARY TABLE [IF NOT EXISTS] table_name
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) [ENGINE = engine]
```

In most cases, temporary tables are not created manually, but when using external data for a query, or for distributed `(GLOBAL) IN`. For more information, see the appropriate sections

It's possible to use tables with [ENGINE = Memory](/docs/engines/table-engines/special/memory) instead of temporary tables.

## REPLACE TABLE[​](#replace-table "Direct link to REPLACE TABLE")

The `REPLACE` statement allows you to update a table [atomically](/docs/concepts/glossary#atomicity).

Note

This statement is supported for the [`Atomic`](/docs/engines/database-engines/atomic) and [`Replicated`](/docs/engines/database-engines/replicated) database engines,
which are the default database engines for ClickHouse and ClickHouse Cloud respectively.

Ordinarily, if you need to delete some data from a table,
you can create a new table and fill it with a `SELECT` statement that does not retrieve unwanted data,
then drop the old table and rename the new one.
This approach is demonstrated in the example below:

```
CREATE TABLE myNewTable AS myOldTable;

INSERT INTO myNewTable
SELECT * FROM myOldTable 
WHERE CounterID <12345;

DROP TABLE myOldTable;

RENAME TABLE myNewTable TO myOldTable;
```

Instead of the approach above, it is also possible to use `REPLACE` (given you are using the default database engines) to achieve the same result:

```
REPLACE TABLE myOldTable
ENGINE = MergeTree()
ORDER BY CounterID 
AS
SELECT * FROM myOldTable
WHERE CounterID <12345;
```

### Syntax[​](#syntax "Direct link to Syntax")

```
{CREATE [OR REPLACE] | REPLACE} TABLE [db.]table_name
```

Note

All syntax forms for the `CREATE` statement also work for this statement. Invoking `REPLACE` for a non-existent table will cause an error.

### Examples:[​](#examples "Direct link to Examples:")

- Local- Cloud

Consider the following table:

```
CREATE DATABASE base 
ENGINE = Atomic;

CREATE OR REPLACE TABLE base.t1
(
    n UInt64,
    s String
)
ENGINE = MergeTree
ORDER BY n;

INSERT INTO base.t1 VALUES (1, 'test');

SELECT * FROM base.t1;

┌─n─┬─s────┐
│ 1 │ test │
└───┴──────┘
```

We can use the `REPLACE` statement to clear all the data:

```
CREATE OR REPLACE TABLE base.t1 
(
    n UInt64,
    s Nullable(String)
)
ENGINE = MergeTree
ORDER BY n;

INSERT INTO base.t1 VALUES (2, null);

SELECT * FROM base.t1;

┌─n─┬─s──┐
│ 2 │ \N │
└───┴────┘
```

Or we can use the `REPLACE` statement to change the table structure:

```
REPLACE TABLE base.t1 (n UInt64) 
ENGINE = MergeTree 
ORDER BY n;

INSERT INTO base.t1 VALUES (3);

SELECT * FROM base.t1;

┌─n─┐
│ 3 │
└───┘
```

Consider the following table on ClickHouse Cloud:

```
CREATE DATABASE base;

CREATE OR REPLACE TABLE base.t1 
(
    n UInt64,
    s String
)
ENGINE = MergeTree
ORDER BY n;

INSERT INTO base.t1 VALUES (1, 'test');

SELECT * FROM base.t1;

1    test
```

We can use the `REPLACE` statement to clear all the data:

```
CREATE OR REPLACE TABLE base.t1 
(
    n UInt64, 
    s Nullable(String)
)
ENGINE = MergeTree
ORDER BY n;

INSERT INTO base.t1 VALUES (2, null);

SELECT * FROM base.t1;

2
```

Or we can use the `REPLACE` statement to change the table structure:

```
REPLACE TABLE base.t1 (n UInt64) 
ENGINE = MergeTree 
ORDER BY n;

INSERT INTO base.t1 VALUES (3);

SELECT * FROM base.t1;

3
```

## COMMENT Clause[​](#comment-clause "Direct link to COMMENT Clause")

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

**Syntax**

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

**Example**

Query: