openGauss插件使用指南:SQL语法—CREATE TABLE PARTITION（5）

• 示例3：START END语法创建、修改Range分区表。

  假定/home/omm/startend_tbs1、/home/omm/startend_tbs2、/home/omm/startend_tbs3、/home/omm/startend_tbs4是omm用户拥有读写权限的空目录。

  -- 创建表空间
  openGauss=# CREATE TABLESPACE startend_tbs1 LOCATION '/home/omm/startend_tbs1';
  openGauss=# CREATE TABLESPACE startend_tbs2 LOCATION '/home/omm/startend_tbs2';
  openGauss=# CREATE TABLESPACE startend_tbs3 LOCATION '/home/omm/startend_tbs3';
  openGauss=# CREATE TABLESPACE startend_tbs4 LOCATION '/home/omm/startend_tbs4';
  
  -- 创建临时schema
  openGauss=# CREATE SCHEMA tpcds;
  openGauss=# SET CURRENT_SCHEMA TO tpcds;
  
  -- 创建分区表，分区键是integer类型
  openGauss=# CREATE TABLE tpcds.startend_pt (c1 INT, c2 INT) 
  TABLESPACE startend_tbs1 
  PARTITION BY RANGE (c2) (
      PARTITION p1 START(1) END(1000) EVERY(200) TABLESPACE startend_tbs2,
      PARTITION p2 END(2000),
      PARTITION p3 START(2000) END(2500) TABLESPACE startend_tbs3,
      PARTITION p4 START(2500),
      PARTITION p5 START(3000) END(5000) EVERY(1000) TABLESPACE startend_tbs4
  )
  ENABLE ROW MOVEMENT;
  
  -- 查看分区表信息
  openGauss=# SELECT relname, boundaries, spcname FROM pg_partition p JOIN pg_tablespace t ON p.reltablespace=t.oid and p.parentid='tpcds.startend_pt'::regclass ORDER BY 1;
     relname   | boundaries |    spcname
  -------------+------------+---------------
   p1_0        | {1}        | startend_tbs2
   p1_1        | {201}      | startend_tbs2
   p1_2        | {401}      | startend_tbs2
   p1_3        | {601}      | startend_tbs2
   p1_4        | {801}      | startend_tbs2
   p1_5        | {1000}     | startend_tbs2
   p2          | {2000}     | startend_tbs1
   p3          | {2500}     | startend_tbs3
   p4          | {3000}     | startend_tbs1
   p5_1        | {4000}     | startend_tbs4
   p5_2        | {5000}     | startend_tbs4
   startend_pt |            | startend_tbs1
  (12 rows)
  
  -- 导入数据，查看分区数据量
  openGauss=# INSERT INTO tpcds.startend_pt VALUES (GENERATE_SERIES(0, 4999), GENERATE_SERIES(0, 4999));
  openGauss=# SELECT COUNT(*) FROM tpcds.startend_pt PARTITION FOR (0);
   count
  -------
       1
  (1 row)
  
  openGauss=# SELECT COUNT(*) FROM tpcds.startend_pt PARTITION (p3);
   count
  -------
     500
  (1 row)
  
  -- 增加分区: [5000, 5300), [5300, 5600), [5600, 5900), [5900, 6000)
  openGauss=# ALTER TABLE tpcds.startend_pt ADD PARTITION p6 START(5000) END(6000) EVERY(300) TABLESPACE startend_tbs4;
  
  -- 增加MAXVALUE分区: p7
  openGauss=# ALTER TABLE tpcds.startend_pt ADD PARTITION p7 END(MAXVALUE);
  
  -- 重命名分区p7为p8
  openGauss=# ALTER TABLE tpcds.startend_pt RENAME PARTITION p7 TO p8;
  
  -- 删除分区p8
  openGauss=# ALTER TABLE tpcds.startend_pt DROP PARTITION p8;
  
  -- 重命名5950所在的分区为：p71
  openGauss=# ALTER TABLE tpcds.startend_pt RENAME PARTITION FOR(5950) TO p71;
  
  -- 分裂4500所在的分区[4000, 5000)
  openGauss=# ALTER TABLE tpcds.startend_pt SPLIT PARTITION FOR(4500) INTO(PARTITION q1 START(4000) END(5000) EVERY(250) TABLESPACE startend_tbs3);
  
  -- 修改分区p2的表空间为startend_tbs4
  openGauss=# ALTER TABLE tpcds.startend_pt MOVE PARTITION p2 TABLESPACE startend_tbs4;
  
  -- 查看分区情形
  openGauss=# SELECT relname, boundaries, spcname FROM pg_partition p JOIN pg_tablespace t ON p.reltablespace=t.oid and p.parentid='tpcds.startend_pt'::regclass ORDER BY 1;
     relname   | boundaries |    spcname
  -------------+------------+---------------
   p1_0        | {1}        | startend_tbs2
   p1_1        | {201}      | startend_tbs2
   p1_2        | {401}      | startend_tbs2
   p1_3        | {601}      | startend_tbs2
   p1_4        | {801}      | startend_tbs2
   p1_5        | {1000}     | startend_tbs2
   p2          | {2000}     | startend_tbs4
   p3          | {2500}     | startend_tbs3
   p4          | {3000}     | startend_tbs1
   p5_1        | {4000}     | startend_tbs4
   p6_1        | {5300}     | startend_tbs4
   p6_2        | {5600}     | startend_tbs4
   p6_3        | {5900}     | startend_tbs4
   p71         | {6000}     | startend_tbs4
   q1_1        | {4250}     | startend_tbs3
   q1_2        | {4500}     | startend_tbs3
   q1_3        | {4750}     | startend_tbs3
   q1_4        | {5000}     | startend_tbs3
   startend_pt |            | startend_tbs1
  (19 rows)
  
  -- 删除表和表空间
  openGauss=# DROP SCHEMA tpcds CASCADE;
  openGauss=# DROP TABLESPACE startend_tbs1;
  openGauss=# DROP TABLESPACE startend_tbs2;
  openGauss=# DROP TABLESPACE startend_tbs3;
  openGauss=# DROP TABLESPACE startend_tbs4;