DETACH Statement

Documentation for Detach

Makes the server “forget” about the existence of a table, a materialized view, a dictionary, or a database.

Syntax

DETACH TABLE|VIEW|DICTIONARY|DATABASE [IF EXISTS] [db.]name [ON CLUSTER cluster] [PERMANENTLY] [SYNC]

Detaching does not delete the data or metadata of a table, a materialized view, a dictionary or a database. If an entity was not detached PERMANENTLY, on the next server launch the server will read the metadata and recall the table/view/dictionary/database again. If an entity was detached PERMANENTLY, there will be no automatic recall.

Whether a table, a dictionary or a database was detached permanently or not, in both cases you can reattach them using the ATTACH query. System log tables can be also attached back (e.g. query_log, text_log, etc.). Other system tables can’t be reattached. On the next server launch the server will recall those tables again.

ATTACH MATERIALIZED VIEW does not work with short syntax (without SELECT), but you can attach it using the ATTACH TABLE query.

Note that you can not detach permanently the table which is already detached (temporary). But you can attach it back and then detach permanently again.

Also, you can not DROP the detached table, or CREATE TABLE with the same name as detached permanently, or replace it with the other table with RENAME TABLE query.

The SYNC modifier executes the action without delay.

Example

Creating a table:

Query:

CREATE TABLE test ENGINE = MergeTree ORDER BY () AS SELECT * FROM numbers(10);
SELECT * FROM test;

Result:

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

Detaching the table:

Query:

DETACH TABLE test;
SELECT * FROM test;

Result:

Received exception from server (version 21.4.1):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.test does not exist.

In ClickHouse Cloud users should use the PERMANENTLY clause e.g. DETACH TABLE <table> PERMANENTLY. If this clause is not used, tables will be reattached on cluster restart e.g. during upgrades.

See Also

• Materialized View
• Dictionaries