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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
298次阅读
2025-04-17 10:41:41
openGauss荣获中国软件行业协会多奖项，技术升级再创行业新高度
openGauss
238次阅读
2025-04-30 14:30:58
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
196次阅读
2025-04-16 09:52:02
GitCode 成 openGauss 新归宿，国产开源数据库里程碑事件
严少安
152次阅读
2025-04-27 11:37:53
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
132次阅读
2025-04-23 17:39:13
单个执行机并行执行MySQL到openGauss数据迁移子任务
Clipnosis
117次阅读
2025-04-30 16:39:58
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
85次阅读
2025-04-18 10:49:53
opengauss使用gs_probackup进行增量备份恢复
进击的CJR
79次阅读
2025-04-09 16:11:58
Postgresql数据库单个Page最多存储多少行数据
maozicb
74次阅读
2025-04-23 16:02:19
RISC-V 首迎 openGauss 7.0.0-RC1 全量版适配！数据库核心功能完整落地开源架构
openGauss
42次阅读
2025-04-16 10:33:59
