TRUNCATE操作
更(geng)新時間 2025-02-14 10:22:09
最近更新(xin)時間: 2025-02-14 10:22:09
分(fen)享文章
truncate功能用于對表(biao)(biao)數據進行快速清除(chu)，truncate 屬于ddl級(ji)別，會給truncate表(biao)(biao)加上 ACCESS EXCLUSIVE 最高(gao)級(ji)別的(de)(de)鎖。本(ben)文為您介紹具體的(de)(de)使用方法。
truncate 普(pu)通(tong)表
使用(yong)語法(fa)：truncate table xx yy zz；
具體例子(zi)如下(xia)所示(shi)：
teledb=# truncate table teledb_pg1;
TRUNCATE TABLE也可以一次truncate 多(duo)個數據表(biao)。
teledb=# truncate table t_update, teledb_serial;
TRUNCATE TABLEtruncate 分區表(biao)
不允許truncate主表
teledb=# truncate table t_range; ERROR: trancate a partitioned table is forbidden, trancate a partition is allowed使用語法：truncate xx partition for(x)，具體(ti)例(li)子如下所示：
truncate 一(yi)個時間分區表。
teledb=# \d+ t_time_range Table "public.t_time_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------------+-----------+----------+---------+---------+--------------+------------- f1 | bigint | | | | plain | | f2 | timestamp without time zone | | | | plain | | f3 | bigint | | | | plain | | Distribute By: SHARD(f1) Location Nodes: ALL DATANODES Partition By: RANGE(f2) # Of Partitions: 12 Start With: 2017-09-01 Interval Of Partition: 1 MONTH teledb=# select * from t_time_range; f1 | f2 | f3 ----+---------------------+----- 1 | 2017-09-01 00:00:00 | 100 1 | 2017-10-01 00:00:00 | 100 1 | 2017-11-01 00:00:00 | 100 (3 rows) teledb=# truncate t_time_range partition for ('2017-09-01' ::timestamp without time zone); TRUNCATE TABLE teledb=# select * from t_time_range; f1 | f2 | f3 ----+---------------------+----- 1 | 2017-10-01 00:00:00 | 100 1 | 2017-11-01 00:00:00 | 100 (2 rows)truncate 一(yi)個數字分區(qu)表。
teledb=# select * from t_range; f1 | f2 | f3 ----+----------------------------+----- 1 | 2023-08-23 10:29:46.263768 | 1 2 | 2023-08-23 10:29:46.263768 | 50 2 | 2023-08-23 10:29:46.263768 | 110 3 | 2023-08-23 10:29:46.263768 | 100 (4 rows) teledb=# \d+ t_range Table "public.t_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------------+-----------+----------+---------+---------+--------------+------------- f1 | bigint | | | | plain | | f2 | timestamp without time zone | | | now() | plain | | f3 | integer | | | | plain | | Distribute By: SHARD(f1) Location Nodes: ALL DATANODES Partition By: RANGE(f3) # Of Partitions: 3 Start With: 1 Interval Of Partition: 50 teledb=# truncate t_range partition for (1); TRUNCATE TABLE teledb=# truncate t_range partition for (2); TRUNCATE TABLE teledb=# select * from t_range; f1 | f2 | f3 ----+----------------------------+---------------- 2 | 2023-08-23 10:29:46.263768 | 110 3 | 2023-08-23 10:29:46.263768 | 100 (2 rows)
