云数据库 GaussDB-CREATE TABLE PARTITION AS:示例

时间：2025-01-26 10:48:33

云数据库 GaussDB

示例

创建一级分区表，并使用子查询数据填充分区表。

-- 创建一个源表t1，并插入3条数据。gaussdb=# CREATE TABLE t1(a int, b int) DISTRIBUTE BY HASH(a);gaussdb=# INSERT INTO t1 VALUES(8, 2);gaussdb=# INSERT INTO t1 VALUES(18, 3);gaussdb=# INSERT INTO t1 VALUES(28, 4);-- 使用CREATE TABLE PARTITION AS创建一级分区表，并使用子查询数据填充分区表gaussdb=# CREATE TABLE t1_part_dup DISTRIBUTE BY HASH(a) PARTITION BY RANGE(a)(    PARTITION p1 VALUES LESS THAN(10),    PARTITION p2 VALUES LESS THAN(20),    PARTITION p3 VALUES LESS THAN(MAXVALUE)) AS SELECT * FROM t1;gaussdb=# \d+ t1_part_dup;          Table "create_table_as_partition_by.t1_part_dup" Column |  Type   | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- a      | integer |           | plain   |              |  b      | integer |           | plain   |              | Partition By RANGE(a)Number of partitions: 3 (View pg_partition to check each partition range.)Has OIDs: noDistribute By: HASH(a)Location Nodes: ALL DATANODESOptions: orientation=row, compression=nogaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p1) ORDER BY a; a | b ---+--- 8 | 2(1 row)gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p2) ORDER BY a; a  | b ----+--- 18 | 3(1 row)gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p3) ORDER BY a; a  | b ----+--- 28 | 4(1 row)gaussdb=# DROP TABLE t1, t1_part_dup;