TRUNCATE操作
更新時間 2025-02-05 09:36:43
最近更新時間: 2025-02-05 09:36:43
分享文章
truncate功能用于對表數據進行快速清除，truncate 屬于ddl級別，會給truncate表加上 ACCESS EXCLUSIVE 最高級別的鎖。本文為您介紹具體的使用方法。
truncate 普通表
使用語法：truncate table xx yy zz；
具體例子如下所示：
teledb=# truncate table teledb_pg1;
TRUNCATE TABLE
也可以一次truncate 多個數據表。
teledb=# truncate table t_update, teledb_serial;
TRUNCATE TABLE
truncate 分區表
-
不允許truncate主表
teledb=# truncate table t_range; ERROR: trancate a partitioned table is forbidden, trancate a partition is allowed使用語法：truncate xx partition for(x)
具體例子如下所示：
-
truncate 一個時間分區表。
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 一個數字分區表。
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)
