云数据库 GaussDB-TRUNCATE:示例

时间：2025-02-12 15:08:13

云数据库 GaussDB

示例

清理表数据

    
         --创建表reason。gaussdb=# CREATE TABLE reason (r_reason_sk int,r_reason_id varchar(16),r_reason_desc varchar(100));--向表中插入多条记录。gaussdb=# INSERT INTO reason values(1,'AAAAAAAABAAAAAAA','reason 1'),  (5,'AAAAAAAABAAAAAAA','reason 2'),                                    (15,'AAAAAAAABAAAAAAA','reason 3'), (25,'AAAAAAAABAAAAAAA','reason 4'),                                   (35,'AAAAAAAABAAAAAAA','reason 5'), (45,'AAAAAAAACAAAAAAA','reason 6');--查看表的信息，大小约为16kBgaussdb=# \d+                                    List of relations Schema |  Name  | Type  | Owner | Size  |             Storage              | Description --------+--------+-------+-------+-------+----------------------------------+------------- public | reason | table | omm   | 16 kB | {orientation=row,compression=no} | (1 row)--使用DELETE语句不带WHERE条件，清空表的数据，并查看表的大小。gaussdb=# DELETE FROM reason;gaussdb=# \d+                                    List of relations Schema |  Name  | Type  | Owner | Size  |             Storage              | Description --------+--------+-------+-------+-------+----------------------------------+------------- public | reason | table | omm   | 16 kB | {orientation=row,compression=no} | (1 row)--使用TRUNCATE清空表reason，并查看表的大小gaussdb=# TRUNCATE TABLE reason;gaussdb=# \d+                                     List of relations Schema |  Name  | Type  | Owner |  Size   |             Storage              | Description --------+--------+-------+-------+---------+----------------------------------+------------- public | reason | table | omm   | 0 bytes | {orientation=row,compression=no} | (1 row)--删除表。gaussdb=# DROP TABLE reason;

清理分区表数据。

    
         --创建分区表。gaussdb=# CREATE TABLE reason_p(    r_reason_sk integer,    r_reason_id character(16),    r_reason_desc character(100))PARTITION BY RANGE (r_reason_sk)(    partition p_05_before values less than (05),    partition p_15 values less than (15),    partition p_25 values less than (25),    partition p_35 values less than (35),    partition p_45_after values less than (MAXVALUE));--插入数据。gaussdb=# INSERT INTO reason_p values(1,'AAAAAAAABAAAAAAA','reason 1'),  (5,'AAAAAAAABAAAAAAA','reason 2'),                                      (15,'AAAAAAAABAAAAAAA','reason 3'), (25,'AAAAAAAABAAAAAAA','reason 4'),                                     (35,'AAAAAAAABAAAAAAA','reason 5'), (45,'AAAAAAAACAAAAAAA','reason 6');--清空分区p_05_before。gaussdb=# ALTER TABLE reason_p TRUNCATE PARTITION p_05_before UPDATE GLOBAL INDEX;--清空分区p_15。gaussdb=# ALTER TABLE reason_p TRUNCATE PARTITION for (13) UPDATE GLOBAL INDEX;--清空分区表。gaussdb=# TRUNCATE TABLE reason_p;--删除表reason_p。gaussdb=# DROP TABLE reason_p;