学到分区表了！查看一下具体的练习如下
1.登录数据库环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
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.
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));
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.查看分区表视图pg_partition
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples
| relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudes
cidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions
| relfrozenxid64
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------
+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+---------
-----+--------------+----------+---------+--------------------+----------+------------+---------+-----------------------------------
----------------+----------------
update_table | r | 16489 | 0 | 0 | r | 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,compression=no,wa
it_clean_gpi=n} | 0
update_table_p0 | p | 16489 | 0 | 0 | r | 16493 | 0 | 0 | 0
update_table_p1 | p | 16489 | 0 | 0 | r | 16494 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9424 | | | | | {50} | | {orientation=row,compression=no}
| 9424
| 9424
update_table_p2 | p | 16489 | 0 | 0 | r | 16495 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9424 | | | | | {150} | | {orientation=row,compression=no}
--More--| 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9424 | | | | | {100} | | {orientation=row,compression=no}
...skipping 1 line
(4 rows)
5.插入分区表数据
omm=# insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');
INSERT 0 3
omm=# insert into update_table values (150, 'd');
ERROR: inserted partition key does not map to any table partition
6.查看第一个分区表的数据
omm=# select * from update_table partition(update_table_p0);
omm=# c1 | c2
----+----
1 | a
(1 row)
7.添加分区表
omm=# alter table update_table add partition update_table_p3 values less than (200);
ALTER TABLE
插入数据
omm=# insert into update_table values (150, 'd');
INSERT 0 1
8.重命名分区表
omm=# alter table update_table rename partition update_table_p1 to update_table_p1_1;
ALTER TABLE
9.删除分区表的第一个分区
omm=# alter table update_table drop partition update_table_p0;
ALTER TABLE
10.删除分区表
omm=# drop table update_table;
DROP TABLE
omm=#
作业
create table store
(
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),
partition update_table_p3 values less than (200),
partition update_table_p4 values less than (250)
);
insert into store values(1,'aa'),(51,'bb'),(101,'cc'),(151,'dd'),(201,'ee');
omm=# select * from store partition(update_table_p0);
c1 | c2
----+----
1 | aa
(1 row)
重命名分区2
alter table store rename partition update_table_p1 to update_table_p1_1;
删除分区5
alter table store drop partition update_table_p4;
增加分区6
alter table store add partition update_table_p5 values less than (300);
查看系统表pg_partition中查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuple
s | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcud
escidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions
| relfrozenxid64
-------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+---------
--+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+-------
-------+--------------+----------+---------+--------------------+----------+------------+---------+---------------------------------
------------------+----------------
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,compression=no,
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9431 | | | | | {50} | | {orientation=row,compression=no}
| 9431
update_table_p2 | p | 16497 | 0 | 0 | r | 16503 | 0 | 0 |
wait_clean_gpi=n} | 0
update_table_p0 | p | 16497 | 0 | 0 | r | 16501 | 0 | 0 |
store | r | 16497 | 0 | 0 | r | 0 | 0 | 0 |
0 | 9431 | | | | | {150} | | {orientation=row,compression=no}
| 9431
update_table_p3 | p | 16497 | 0 | 0 | r | 16504 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9431 | | | | | {200} | | {orientation=row,compression=no}
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
| 9431
update_table_p1_1 | p | 16497 | 0 | 0 | r | 16502 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9431 | | | | | {100} | | {orientation=row,compression=no}
| 9431
update_table_p5 | p | 16497 | 0 | 0 | r | 16506 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9435 | | | | | {300} | | {orientation=row,compression=no}
| 9435
(6 rows)
删除分区表
omm=# drop table store;
DROP TABLE
omm=#
作业中没有加注释的，应该看的懂了吧，如果看不懂，请查看第一步实操部分，多加练习
