openGauss分区表
学习内容
1、分区表创建
create table 表名 (id int, name char(20))
partition by range(列名，如id)
(
partition 分区名1 values 数据范围,
partition 分区名2 values 数据范围,
partition 分区名3 values 数据范围,
partition 分区名n values 数据范围
eg: partition tb_part1 values less than (100);
);
2、分区表alter操作（增删改）
增加分区
alter table 表名 add 【partition store_partition_p6 values bigger than(300)】; /*全角中括号里面的内容和创建的时候一致*/
删除分区
alter table 表名 drop partition 分区名称;
重命名分区
alter table 表名 rename partition 分区名 to 分区新名字; /*注意rename和partition的位置*/
3、查看分区表
系统表pg_partition
select * from pg_partition;
查看分区表概况
\d+ 表名
查看某一分区数据
select * from 表名 partition(分区名字);
4、删除表
drop table 表名;
作业内容
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
create table store(id int, name char(20)) partition by range(id)
(
partition store_partition_p1 values less than (50),
partition store_partition_p2 values less than (100),
partition store_partition_p3 values less than (150),
partition store_partition_p4 values less than (200),
partition store_partition_p5 values less than (250)
);
insert into store values (32,'liming'),(78,'bucunzi'),(123,'zhansd'),(235,'ttlx'),(183,'hcscx');
效果
omm=# create table store(id int, name char(20)) partition by range(id)
omm-# (
omm(# partition store_partition_p1 values less than (50),
omm(# partition store_partition_p2 values less than (100),
omm(# partition store_partition_p3 values less than (150),
omm(# partition store_partition_p4 values less than (200),
omm(# partition store_partition_p5 values less than (250)
omm(# );
CREATE TABLE
omm=# insert into store values (32,'liming'),(78,'bucunzi'),(123,'zhansd'),(235,'ttlx'),(183,'hcscx');
INSERT 0 5
2.查看分区1上的数据
\d+ store;
select * from store partition(store_partition_p1);
效果
omm=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(20) | | extended | |
Range partition by(id)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
^
omm=# select * from store partition(store_partition_p1);
id | name
----+----------------------
32 | liming
(1 row)
3.重命名分区2
alter table store rename partition store_partition_p1 to store_partition_p1new;
效果
omm=# alter table store rename partition store_partition_p1 to store_partition_p1new;
ALTER TABLE
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastreli
d | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | in
terval | boundaries | transit | reloptions | relfrozenxid64
-----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+-------------
--+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+---
-------+------------+---------+---------------------------------------------------+----------------
store | r | 16389 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | |
store_partition_p2 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 |
| | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {150} | | {orientation=row,compression=no} | 9034
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {100} | | {orientation=row,compression=no} | 9034
store_partition_p3 | p | 16389 | 0 | 0 | r | 16395 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {200} | | {orientation=row,compression=no} | 9034
store_partition_p5 | p | 16389 | 0 | 0 | r | 16397 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {250} | | {orientation=row,compression=no} | 9034
store_partition_p1new | p | 16389 | 0 | 0 | r | 16393 | 0 | 0 | 0 | 0 |
store_partition_p4 | p | 16389 | 0 | 0 | r | 16396 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
...skipping 1 line
(6 rows)
由上面的分区信息可以看到分区已经更名
4.删除分区5
alter table store drop partition store_partition_p5;
效果
omm=# alter table store drop partition store_partition_p5;
ALTER TABLE
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastreli
d | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | in
terval | boundaries | transit | reloptions | relfrozenxid64
-----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+-------------
--+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+---
-------+------------+---------+---------------------------------------------------+----------------
store | 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
store_partition_p2 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {100} | | {orientation=row,compression=no} | 9034
store_partition_p3 | 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
store_partition_p4 | p | 16389 | 0 | 0 | r | 16396 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {200} | | {orientation=row,compression=no} | 9034
store_partition_p1new | 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
(5 rows)
可以看到已经删除分区5
5.增加分区6
alter table store add partition store_partition_p6 values less than(300);
insert into store values (288,'dashuju'); /*数据插入测试*/
效果
omm=# insert into store values (288,'dashuju');
ERROR: inserted partition key does not map to any table partition
omm=# alter table store add partition store_partition_p6 values less than(300);
ALTER TABLE
omm=# insert into store values (288,'dashuju');
INSERT 0 1
omm=#
6.在系统表pg_partition中查看分区信息
select * from pg_partition;
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastreli
d | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | in
terval | boundaries | transit | reloptions | relfrozenxid64
-----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+-------------
--+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+---
-------+------------+---------+---------------------------------------------------+----------------
store | 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
store_partition_p2 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {100} | | {orientation=row,compression=no} | 9034
store_partition_p3 | 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
store_partition_p4 | p | 16389 | 0 | 0 | r | 16396 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | |
| {200} | | {orientation=row,compression=no} | 9034
store_partition_p1new | 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
store_partition_p6 | p | 16389 | 0 | 0 | r | 16398 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9323 | | | |
| {300} | | {orientation=row,compression=no} | 9323
(6 rows)
omm=#
7.删除分区表
drop table store;
效果
omm=# drop table store;
DROP TABLE
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastid
xid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | bound
aries | transit | reloptions | relfrozenxid64
---------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+-----------
----+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------
------+---------+------------+----------------
(0 rows)
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
