Makes the server "forget" about the existence of the table or materialized view.
DETACH TABLE|VIEW [IF EXISTS] [db.]name [PERMANENTLY] [ON CLUSTER cluster]
Detaching does not delete the data or metadata for the table or materialized view. If the table or view was not detached
PERMANENTLY, on the next server launch the server will read the metadata and recall the table/view again. If the table or view was detached
PERMANENTLY, there will be no automatic recall.
Whether the table was detached permanently or not, in both cases you can reattach it using the ATTACH. System log tables can be also attached back (e.g.
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 doesn't 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.
Creating a table:
CREATE TABLE test ENGINE = Log AS SELECT * FROM numbers(10); SELECT * FROM test;
┌─number─┐ │ 0 │ │ 1 │ │ 2 │ │ 3 │ │ 4 │ │ 5 │ │ 6 │ │ 7 │ │ 8 │ │ 9 │ └────────┘
Detaching the table:
DETACH TABLE test; SELECT * FROM test;
Received exception from server (version 21.4.1): Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.test doesn't exist.