示例

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

  -- 创建一个源表t1，并插入3条数据。
  gaussdb=# CREATE TABLE t1(a int, b int);
  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 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: no
  Options: orientation=row, compression=no
  gaussdb=# 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;

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

  -- 创建一个源表t1，并插入6条数据
  gaussdb=# CREATE TABLE t1(a int, b int, c int, d varchar, e varchar) PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b)
  (
      PARTITION p1 VALUES LESS THAN(200)
      (SUBPARTITION s1 VALUES LESS THAN (500),
       SUBPARTITION s2 VALUES LESS THAN (MAXVALUE)
      ),
      PARTITION p2 VALUES LESS THAN(500)
      (SUBPARTITION s3 VALUES LESS THAN (500),
       SUBPARTITION s4 VALUES LESS THAN (MAXVALUE)
      ),
      PARTITION p3 VALUES LESS THAN(MAXVALUE)
      (SUBPARTITION s5 VALUES LESS THAN (500),
       SUBPARTITION s6 VALUES LESS THAN (MAXVALUE)
      )
  );
  
  gaussdb=# INSERT INTO t1 VALUES (100, 300, 1, 'aA');
  gaussdb=# INSERT INTO t1 VALUES (150, 550, 1, 'bB');
  gaussdb=# INSERT INTO t1 VALUES (250, 150, 1, 'cC');
  gaussdb=# INSERT INTO t1 VALUES (350, 560, 1, 'dD');
  gaussdb=# INSERT INTO t1 VALUES (550, 100, 1, 'eE');
  gaussdb=# INSERT INTO t1 VALUES (650, 600, 1, 'fF');
  
  -- 使用CREATE TABLE SUBPARTITION AS创建二级分区表，并使用子查询数据填充分区表。
  gaussdb=# CREATE TABLE t1_part_dup PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b)
  (
      PARTITION p1 VALUES LESS THAN(200)
      (SUBPARTITION s1 VALUES LESS THAN (500),
       SUBPARTITION s2 VALUES LESS THAN (MAXVALUE)
      ),
      PARTITION p2 VALUES LESS THAN(500)
      (SUBPARTITION s3 VALUES LESS THAN (500),
       SUBPARTITION s4 VALUES LESS THAN (MAXVALUE)
      ),
      PARTITION p3 VALUES LESS THAN(MAXVALUE)
      (SUBPARTITION s5 VALUES LESS THAN (500),
       SUBPARTITION s6 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    |              | 
   c      | integer           |           | plain    |              | 
   d      | character varying |           | extended |              | 
   e      | character varying |           | extended |              | 
  Partition By RANGE(a) Subpartition By RANGE(b)
  Number of partitions: 3 (View pg_partition to check each partition range.)
  Number of subpartitions: 6 (View pg_partition to check each subpartition range.)
  Has OIDs: no
  Options: orientation=row, compression=no
  gaussdb=# SELECT a, b, c, d, e FROM t1_part_dup ORDER BY a;
    a  |  b  | c | d  | e 
  -----+-----+---+----+---
   100 | 300 | 1 | aA | 
   150 | 550 | 1 | bB | 
   250 | 150 | 1 | cC | 
   350 | 560 | 1 | dD | 
   550 | 100 | 1 | eE | 
   650 | 600 | 1 | fF | 
  (6 rows)
  gaussdb=# SELECT a, b, c, d, e FROM t1_part_dup PARTITION(p1) ORDER BY a;
    a  |  b  | c | d  | e 
  -----+-----+---+----+---
   100 | 300 | 1 | aA | 
   150 | 550 | 1 | bB | 
  (2 rows)
  gaussdb=# SELECT a, b, c, d, e FROM t1_part_dup PARTITION(s1) ORDER BY a;
    a  |  b  | c | d  | e 
  -----+-----+---+----+---
   100 | 300 | 1 | aA | 
  (1 row)
  gaussdb=# DROP TABLE t1, t1_part_dup;