Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

CREATE TEMPORARY TABLE

View as MarkdownEdit this pageReport an issueSet up docs MCP

Temporary table support

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.

Syntax

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 instead of temporary tables.

Navigation