truncate 操作

最近更新时间:2023-04-18 10:37:35

我的收藏
truncate 功能用于对表数据进行快速清除,truncate 属于 ddl 级别,会给 truncate 表加上 ACCESS EXCLUSIVE 最高级别的锁。

truncate 普通表

使用语法:truncate table xx yy zz;具体例子如下所示:
postgres=# truncate table t1;
TRUNCATE TABLE
也可以一次 truncate 多个数据表。
postgres=# truncate table t1,t2;
TRUNCATE TABLE
postgres=#

truncate 分区表

truncate 一个时间分区表。
使用语法:truncate xx partition for(x),具体例子如下所示:
truncate 一个时间分区表
postgres=# \\d+ t_time_range
Table "pgxz.t_time_range"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+--------------+-------------
f1 | bigint | | plain | |
f2 | timestamp without time zone | | plain | |
f3 | character varying(20) | | extended | |
Has OIDs: no
Distribute By SHARD(f1)
Location Nodes: dn001, dn002
Partition By: RANGE(f2)
# Of Partitions: 12
Start With: 2017-09-01
Interval Of Partition: 1 MONTH

postgres=# select * from t_time_range;
f1 | f2 | f3
----+---------------------+-------
1 | 2017-09-01 00:00:00 | tdsql_pg
2 | 2017-10-01 00:00:00 | pgxz
(2 rows)

postgres=# truncate t_time_range partition for ('2017-09-01' ::timestamp without time zone);
TRUNCATE TABLE
postgres=# select * from t_time_range;
f1 | f2 | f3
----+---------------------+------
2 | 2017-10-01 00:00:00 | pgxz
(1 row)

postgres=#
truncate 一个数字分区表。
postgres=# \\d+ t_range
Table "pgxz.t_range"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------------+---------------+---------+--------------+-------------
f1 | integer | | plain | |
f2 | timestamp without time zone | default now() | plain | |
f3 | integer | | plain | |
Has OIDs: no
Distribute By SHARD(f1)
Location Nodes: dn01, dn02
Partition By: RANGE(f3)
# Of Partitions: 3
Start With: 1
Interval Of Partition: 50

postgres=# select * from t_range ;
f1 | f2 | f3
----+----------------------------+-----
1 | 2017-12-22 11:47:39.153234 | 1
2 | 2017-12-22 11:47:39.153234 | 50
2 | 2017-12-22 11:47:39.153234 | 110
3 | 2017-12-22 11:47:39.153234 | 100
(4 rows)

postgres=# truncate t_range partition for (1);
TRUNCATE TABLE
postgres=# select * from t_range ;
f1 | f2 | f3
----+----------------------------+-----
2 | 2017-12-22 11:47:39.153234 | 110
3 | 2017-12-22 11:47:39.153234 | 100
(2 rows)