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 table property auto.purge is set to false by default, deleted data rows are stored in the recycle bin of the file system. Otherwise, the data rows are directly deleted.

Remarks

The target table must be a control table, which means table propery external is set to false. Otherwise, an error will be reported during statement execution.

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)