1.创建分区表
--1、使用gsql登录openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
--2、创建范围分区表
omm=# create table update_table
omm-# (
omm(# c1 int,
omm(# 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
--3、查看分区表信息
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
--4、查询分区表相关视图
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisi
ble | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | in
tspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+-----------
----+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+---
-------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
| 1 | | | | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
update_table_p0 | p | 16404 | 0 | 0 | r | 16408 | 0 | 0 | 0 |
update_table | r | 16404 | 0 | 0 | r | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9328 |
| | | | {50} | | {orientation=row,compression=no} | 9328
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9328 |
update_table_p1 | p | 16404 | 0 | 0 | r | 16409 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9328 |
| | | | {150} | | {orientation=row,compression=no} | 9328
(4 rows)
| | | | {100} | | {orientation=row,compression=no} | 9328
update_table_p2 | p | 16404 | 0 | 0 | r | 16410 | 0 | 0 | 0 |
--5、插入数据
omm=# insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');
INSERT 0 3
--6、查询分区数据
--update_table_p0分区
omm=# select * from update_table partition(update_table_p0);
c1 | c2
----+----
1 | a
(1 row)
--update_table_p1分区
omm=# select * from update_table partition(update_table_p1);
c1 | c2
----+----
50 | b
(1 row)
--update_table_p2分区
omm=# select * from update_table partition(update_table_p2);
c1 | c2
-----+----
100 | c
(1 row)
插入时超出范围的数据会报错：
omm=# insert into update_table values (150, ‘d’);
ERROR: inserted partition key does not map to any table partition
2.创建分区
--1、创建表分区
omm=# alter table update_table add partition update_table_p3 values less than (200);
ALTER TABLE
--2、查询分区表信息
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: 4 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
--3、插入数据
omm=# insert into update_table values (150, 'd');
INSERT 0 1
--4、查询分区数据
omm=# select * from update_table partition(update_table_p3);
omm=# c1 | c2
-----+----
150 | d
(1 row)
3.修改分区属性
--1、将表update_table 的分区update_table_p1 重命名
omm=# alter table update_table rename partition update_table_p1 to update_table_p1_1;
ALTER TABLE
--2、查询分区表信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvi
sible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid |
intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
-------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------
------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+-
---------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
update_table | r | 16412 | 0 | 0 | r | 0 | 0 | 0 | 0 |
update_table_p0 | p | 16412 | 0 | 0 | r | 16416 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 |
| 1 | | | | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9333 |
| | | | {50} | | {orientation=row,compression=no} | 9333
update_table_p2 | p | 16412 | 0 | 0 | r | 16418 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9333 |
| | | | {150} | | {orientation=row,compression=no} | 9333
update_table_p3 | p | 16412 | 0 | 0 | r | 16419 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9335 |
| | | | {200} | | {orientation=row,compression=no} | 9335
update_table_p1_1 | p | 16412 | 0 | 0 | r | 16417 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9333 |
| | | | {100} | | {orientation=row,compression=no} | 9333
(5 rows)
4.删除分区
--1、删除分区表中其中一个分区
omm=# alter table update_table drop partition update_table_p0;
ALTER TABLE
--2、查询分区表信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvi
sible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid |
intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
-------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------
------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+-
---------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
| 1 | | | | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
update_table_p2 | p | 16412 | 0 | 0 | r | 16418 | 0 | 0 | 0 |
update_table | r | 16412 | 0 | 0 | r | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9333 |
update_table_p3 | p | 16412 | 0 | 0 | r | 16419 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9335 |
| | | | {200} | | {orientation=row,compression=no} | 9335
update_table_p1_1 | p | 16412 | 0 | 0 | r | 16417 | 0 | 0 | 0 |
| | | | {150} | | {orientation=row,compression=no} | 9333
| | | | {100} | | {orientation=row,compression=no} | 9333
(4 rows)
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9333 |
5.删除分区表
--1、删除分区表
omm=# drop table update_table;
DROP TABLE
--2、验证表已删除
omm=# \d+
No relations found.
练习：
1.创建一个含有3个分区的范围分区表store，在每个分区中插入记录
--1、创建表
create table store
(
ID NUMBER(10),
NAME CHAR(50)
)
partition by range (ID)
(
partition store_table_p0 values less than (100),
partition store_table_p1 values less than (200),
partition store_table_p2 values less than (300)
);
CREATE TABLE
--2、插入数据
omm=# insert into store values(50,'XIAOLAN');
INSERT 0 1
omm=# insert into store values(150,'XIAOHONG');
INSERT 0 1
omm=# insert into store values(250,'XIAOHEI');
INSERT 0 1
2.查看分区1上的数据
omm=# select * from store partition(store_table_p0);
id | name
----+----------------------------------------------------
50 | XIAOLAN
(1 row)
3.重命名分区1
omm=# alter table store rename partition store_table_p0 to store_table_p3;
ALTER TABLE
4.删除分区2
omm=# alter table store drop partition store_table_p1;
ALTER TABLE
5.增加分区4
omm=# alter table store add partition store_table_p4 values less than (400);
ALTER TABLE
6.在系统表pg_partition中查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisib
le | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | int
spnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+------------
---+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----
------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
store | r | 16420 | 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_table_p2 | p | 16420 | 0 | 0 | r | 16426 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9340 |
| | | | {300} | | {orientation=row,compression=no} | 9340
store_table_p3 | p | 16420 | 0 | 0 | r | 16424 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9340 |
| | | | {100} | | {orientation=row,compression=no} | 9340
store_table_p4 | p | 16420 | 0 | 0 | r | 16427 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9346 |
| | | | {400} | | {orientation=row,compression=no} | 9346
(4 rows)
7.删除分区表
omm=# drop table store;
DROP TABLE
omm=# \dt+
No relations found.
omm=#
最后修改时间：2021-12-10 23:09:16
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
