openGauss每日一练第8天 | 分区表基本操作

1、创建分区表

----范围分区表

create table update_table(c1 int, c2 CHAR(2))
partition by range (c1)
(
        partition update_table_p0 values less than (50),
        partition update_table_p1 values less than (100),
        partition update_table_p2 values less than (150)
);

----查看分区表信息

\d+ update_table;
select relname,parttype,parentid,rangenum,intervalnum,partstrategy from pg_partition;

----插入数据

insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');

--超出范围的数据会报错
insert into update_table values (150, 'd');

----查看各区上的数据

select * from update_table partition(update_table_p0);

2、创建分区

alter table update_table add partition update_table_p3 values less than (200);
insert into update_table values (150, 'd');
select * from update_table partition(update_table_p3);

3、修改分区属性

alter table update_table rename partition update_table_p1 to update_table_p1_1;
select relname,parttype,parentid,rangenum,intervalnum,partstrategy from pg_partition;

4、删除分区

alter table update_table drop partition update_table_p0;
select relname,parttype,parentid,rangenum,intervalnum,partstrategy from pg_partition;

5、删除分区表

drop table update_table;
\d+ update_table

课后作业

1、创建一个含有5个分区的范围分区表store，在每个分区中插入记录

create table store(c1 int, c2 CHAR(2))
partition by range (c1)
(
  partition store_p1 values less than (10),
  partition store_p2 values less than (20),
  partition store_p3 values less than (30),
  partition store_p4 values less than (40),
  partition store_p5 values less than (50)
);
insert into store values (5, 'a'), (15, 'b'), (25, 'c'), (35, 'd'), (45, 'e');

2、查看分区1上的数据

select * from store partition(store_p1);

3、重命名分区2

alter table store rename partition store_p2 to store_p2_1;
select relname,parttype,parentid,rangenum,intervalnum,partstrategy from pg_partition;

4、删除分区5

alter table store drop partition store_p5;
select relname,parttype,parentid,rangenum,intervalnum,partstrategy from pg_partition;

5、增加分区6

alter table store add partition store_p6 values less than (60);
insert into store values (55, 'f');

6、在系统表pg_partition中查看分区信息

\d+ store;
select relname,parttype,parentid,rangenum,intervalnum,partstrategy from pg_partition;

7、删除分区表

drop table store;