TRUNCATE TABLE

Syntax

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

partition_spec:

: (partition_column = partition_col_value, partition_column = partition_col_value, ...)

Description

This statement is used to remove all rows from a table or partition. You can use partition_spec to delete multiple specified partitions of a partitioned table at a time. If this parameter is not specified, all partitions of the partitioned table are deleted at a time. When the recycle bin of the file system is available, the deleted data rows are saved to the recycle bin. Otherwise, the data rows are deleted directly.

Remarks

The target table must be a managed table. Otherwise, an error will be reported during statement execution.

If auto.purge is set to true, the TRUNCATE TABLE statement does not reclaim table data. This function is disabled by default or when auto.purge is set to false.

Example

-- Delete a native or control table
Create table simple(id int, name string);
 
Insert into simple values(1,'abc')(2,'def');
 
select * from simple;
 id | name
----|------
  1 | abc
  2 | def
(2 rows)
 
Truncate table simple;
 
select * from simple;
 id | name
----|------
(0 rows)
 
-- Delete a table partition.
Create table tb_truncate_part (id int, name string) partitioned by (age int, state string);
 
Insert into tb_truncate_part values (1,'abc',10,'ap'),(2,'abc',10,'up'),(3,'abc',20,'ap'),(4,'abc',20,'up');
 
select * from tb_truncate_part;
 id | name | age | state
----|------|-----|-------
  2 | abc  |  10 | up
  3 | abc  |  20 | ap
  1 | abc  |  10 | ap
  4 | abc  |  20 | up
(4 rows
 
Truncate table tb_truncate_part partition (state = 'ap', age = 10);
 
select * from tb_truncate_part;
id | name | age | state
----|------|-----|-------
  4 | abc  |  20 | up
  2 | abc  |  10 | up
  3 | abc  |  20 | ap
(3 rows)