Truncate Table

Truncate allows the data in a table or database to be removed, while preserving their existence.

Truncate allows the data in a table or database to be removed, while preserving their existence. This is a lightweight operation which can’t be reversed.

The TRUNCATE statement in ClickHouse is used to quickly remove all data from a table or database while preserving their structure.

TRUNCATE TABLE

TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster] [SYNC]

Parameter	Description
IF EXISTS	Prevents an error if the table does not exist. If omitted, the query returns an error.
db.name	Optional database name.
ON CLUSTER cluster	Runs the command across a specified cluster.
SYNC	Makes the truncation synchronous across replicas when using replicated tables. If omitted, truncation happens asynchronously by default.

You can use the alter_sync setting to set up waiting for actions to be executed on replicas.

You can specify how long (in seconds) to wait for inactive replicas to execute TRUNCATE queries with the replication_wait_for_inactive_replica_timeout setting.

If the alter_sync is set to 2 and some replicas are not active for more than the time, specified by the replication_wait_for_inactive_replica_timeout setting, then an exception UNFINISHED is thrown.

The TRUNCATE TABLE query is not supported for the following table engines:

• View
• File
• URL
• Buffer
• Null

TRUNCATE ALL TABLES

TRUNCATE [ALL] TABLES FROM [IF EXISTS] db [LIKE | ILIKE | NOT LIKE '<pattern>'] [ON CLUSTER cluster]

Parameter	Description
ALL	Removes data from all tables in the database.
IF EXISTS	Prevents an error if the database does not exist.
db	The database name.
LIKE | ILIKE | NOT LIKE '<pattern>'	Filters tables by pattern.
ON CLUSTER cluster	Runs the command across a cluster.

Removes all data from all tables in a database.

TRUNCATE DATABASE

TRUNCATE DATABASE [IF EXISTS] db [ON CLUSTER cluster]

Parameter	Description
IF EXISTS	Prevents an error if the database does not exist.
db	The database name.
ON CLUSTER cluster	Runs the command across a specified cluster.

Removes all tables from a database but keeps the database itself. When the clause IF EXISTS is omitted, the query returns an error if the database does not exist.

TRUNCATE DATABASE is not supported for Replicated databases. Instead, just DROP and CREATE the database.