openGauss每日一练第8天

1.创建分区表

–范围分区表

omm=# create table update\_table  
omm(# omm-# (  
omm(# c1 int,  
c2 CHAR(2)  
omm(# )  
omm-# partition by range (c1)  
omm-# (  
omm(# partition update\_table\_p0 values less than (50),  
omm(# partition update\_table\_p1 values less than (100),  
omm(# partition update\_table\_p2 values less than (150)  
omm(# );  
CREATE TABLE  

–查看分区表信息

omm=# \\d+ update\_table;  
Table “public.update\_table”  
Column | Type | Modifiers | Storage | Stats target | Description  
\--------±-------------±----------±---------±-------------±------------  
c1 | integer | | plain | |  
c2 | character(2) | | extended | |  
Range partition by(c1)  
Number of partition: 3 (View pg\_partition to check each partition range.)  
Has OIDs: no  
Options: orientation=row, compression=no  
omm=# select \* from pg\_partition;  
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | re  
ltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval  
| boundaries | transit | reloptions | relfrozenxid64  
\-----------------±---------±---------±---------±------------±-------------±------------±--------------±---------±----------±--------------±--------------±–  
\------------±-----------±------------±--------------±------------±---------------±-------------±-------------±---------±--------±-------------------±--------  
\-±-----------±--------±--------------------------------------------------±---------------  
update\_table | r | 16389 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | |  
| | | {orientation=row,compression=no,wait\_clean\_gpi=n} | 0  
update\_table\_p1 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 | 0 |  
update\_table\_p0 | p | 16389 | 0 | 0 | r | 16393 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |  
| {50} | | {orientation=row,compression=no} | 9034  
0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |  
update\_table\_p2 | p | 16389 | 0 | 0 | r | 16395 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |  
| {150} | | {orientation=row,compression=no} | 9034  
(4 rows)  
| {100} | | {orientation=row,compression=no}  

–插入数据

omm=# insert into update\_table values (1, ‘a’), (50, ‘b’), (100, ‘c’);  
INSERT 0 3  

–查看各区上的数据

omm=# select \* from update\_table partition(update\_table\_p0);  
c1 | c2  
\----±—  
1 | a  
(1 row) 

2.创建分区

omm=# alter table update\_table add partition update\_table\_p3 values less than (200);  
ALTER TABLE  
omm=# insert into update\_table values (150, ‘d’);  
omm=# INSERT 0 1

3.修改分区属性

omm=# alter table update\_table rename partition update\_table\_p1 to update\_table\_p1\_1;  
ALTER TABLE  

4.删除分区

omm=# alter table update\_table drop partition update\_table\_p0;  
ALTER TABLE

5.删除分区表

drop table update\_table;

课程作业

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

omm=# create table store(c1 int,c2 CHAR(20))  
omm-# partition by range (c1)  
omm-# (  
omm(# partition table\_p0 values less than (10),  
omm(# partition table\_p1 values less than (20),  
omm(# partition table\_p2 values less than (30),  
partition table\_p3 values less than (40),  
partition table\_p4 values less than (50)  
);  
omm(#  
CREATE TABLE  
omm=# insert into store values (2,‘d’),(12,‘dd’),(24,‘qw’),(32,‘dddfa’),(46,‘wq23’);  
INSERT 0 5

2.查看分区1上的数据

omm=# select \* from store partition(table\_p0);  
omm=# c1 | c2  
\----±---------------------  
2 | d  
(1 row) 

3.重命名分区2

alter table store rename partition table\_p1 to store\_p1;  
omm=# select \* from pg\_partition;  
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoasti  
dxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boun  
daries | transit | reloptions | relfrozenxid64  
\----------±---------±---------±---------±------------±-------------±------------±--------------±---------±----------±--------------±--------------±---------  
\-----±-----------±------------±--------------±------------±---------------±-------------±-------------±---------±--------±-------------------±---------±----  
\-------±--------±--------------------------------------------------±---------------  
store | r | 16397 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | |  
| | {orientation=row,compression=no,wait\_clean\_gpi=n} | 0  
table\_p0 | p | 16397 | 0 | 0 | r | 16401 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {10}  
| | {orientation=row,compression=no} | 9325  
table\_p2 | p | 16397 | 0 | 0 | r | 16403 | 0 | 0 | 0 | 0 | 0 |  
table\_p3 | p | 16397 | 0 | 0 | r | 16404 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {40}  
| | {orientation=row,compression=no} | 9325  
table\_p4 | p | 16397 | 0 | 0 | r | 16405 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {50}  
| | {orientation=row,compression=no} | 9325  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {30}  
| | {orientation=row,compression=no} | 9325  
store\_p1 | p | 16397 | 0 | 0 | r | 16402 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {20}  
| | {orientation=row,compression=no} | 9325  
(6 rows)  

4.删除分区5

omm=# alter table store drop partition table\_p4;  
ALTER TABLE 

5.增加分区6

omm=# alter table store add partition store\_p6 values less than (100);  
ALTER TABLE

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

omm=# select \* from pg\_partition;  
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoasti  
dxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boun  
daries | transit | reloptions | relfrozenxid64  
\----------±---------±---------±---------±------------±-------------±------------±--------------±---------±----------±--------------±--------------±---------  
\-----±-----------±------------±--------------±------------±---------------±-------------±-------------±---------±--------±-------------------±---------±----  
\-------±--------±--------------------------------------------------±---------------  
store | r | 16397 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {10}  
0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | |  
| | {orientation=row,compression=no,wait\_clean\_gpi=n} | 0  
table\_p0 | p | 16397 | 0 | 0 | r | 16401 | 0 | 0 | 0 | 0 | 0 |  
| | {orientation=row,compression=no} | 9325  
table\_p2 | p | 16397 | 0 | 0 | r | 16403 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {30}  
| | {orientation=row,compression=no} | 9325  
table\_p3 | p | 16397 | 0 | 0 | r | 16404 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {40}  
| | {orientation=row,compression=no} | 9325  
store\_p1 | p | 16397 | 0 | 0 | r | 16402 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9325 | | | | | {20}  
| | {orientation=row,compression=no} | 9325  
store\_p6 | p | 16397 | 0 | 0 | r | 16406 | 0 | 0 | 0 | 0 | 0 |  
0 | 0 | t | 0 | 0 | 0 | 0 | 9330 | | | | | {100  
} | | {orientation=row,compression=no} | 9330  
(6 rows)  

7.删除分区表

omm=# drop table store;  
omm=# DROP TABLE