openGauss每日一练第8天
1.创建分区表
–范围分区表
create table update_table
(
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)
);
create table update_table
(
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_tableomm-# _p2 values less than (150)
);
CREATE TABLE
–查看分区表信息
\d+ update_table;
select * from pg_partition;
\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
select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespac
e | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | relde
ltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltables
pace | 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_p0 | p | 16389 | 0 | 0 | r | 16393 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t |
| | {50} | | {orientation=row,compression=no} | 9034
update_table_p1 | p | 16389 | 0 | 0 | r | 16394 |
0 | 0 | 0 | 0 | 9034 | | |
0 | 0 | 0 | 0 | 9034 | | |
| | {100} | | {orientation=row,compression=no} | 9034
0 | 0 | 0 | 0 | 0 | 0 | 0 | t |
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)
–插入数据
insert into update_table values (1, ‘a’), (50, ‘b’), (100, ‘c’);
insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');
INSERT 0 3
–超出范围的数据会报错
insert into update_table values (150, ‘d’);
insert into update_table values (150, 'd');
ERROR: inserted partition key does not map to any table partition
–查看各区上的数据
select * from update_table partition(update_table_p0);
select * from update_table;
c1 | c2
-----+----
1 | a
50 | b
100 | c
(3 rows)
select * from update_table partition(update_table_p0);
c1 | c2
----+----
1 | a
(1 row)
2.创建分区
alter table update_table add partition update_table_p3 values less than (200);
insert into update_table values (150, ‘d’);
alter table update_table add partition update_table_p3 values less than (200);
ALTER TABLE
insert into update_table values (150, 'd');
INSERT 0 1
3.修改分区属性
alter table update_table rename partition update_table_p1 to update_table_p1_1;
alter table update_table rename partition update_table_p1 to update_table_p1_1;
ALTER TABLE
4.删除分区
alter table update_table drop partition update_table_p0;
alter table update_table drop partition update_table_p0;
ALTER TABLE
select * from update_table;
c1 | c2
-----+----
50 | b
100 | c
150 | d
(3 rows)
5.删除分区表
drop table update_table;
drop table update_table;
DROP TABLE
课程作业
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
create table store
(
c1 int,
c2 CHAR(2)
)
partition by range (c1)
(
partition store_p0 values less than (50),
partition store_p1 values less than (100),
partition store_p2 values less than (150),
partition store_p3 values less than (200),
partition store_p4 values less than (250)
);
insert into store values (1, 'a'), (50, 'b'), (100, 'c'), (150, 'c'), (200, 'c');
INSERT 0 5
2.查看分区1上的数据
select * from store partition(store_p1);
c1 | c2
----+----
50 | b
(1 row)
3.重命名分区2
alter table store rename partition store_p1 to store_p11;
ALTER TABLE
4.删除分区5
alter table store drop partition store_p4;
ALTER TABLE
5.增加分区6
alter table store add partition store_p5 values less than(300);
ALTER TABLE
select * from store ;
c1 | c2
-----+----
1 | a
50 | b
100 | c
150 | c
(4 rows)
6.在系统表pg_partition中查看分区信息
select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | re
lpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarel
id | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace |
interval | boundaries | transit | reloptions | relfrozenxid64
-----------+----------+----------+----------+-------------+--------------+-------------+---------------+---
-------+-----------+---------------+---------------+---------------+------------+-------------+------------
---+-------------+----------------+--------------+--------------+----------+---------+--------------------+
----------+------------+---------+---------------------------------------------------+----------------
store | r | 16397 | 0 | 0 | r | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t |
| {50} | | {orientation=row,compression=no} | 9326
0 | 0 | 0 | 0 | 0 | | 1 | |
| | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
store_p0 | p | 16397 | 0 | 0 | r | 16401 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9326 | | | |
0 | 0 | 0 | 0 | 9326 | | | |
store_p2 | p | 16397 | 0 | 0 | r | 16403 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t |
| {150} | | {orientation=row,compression=no} | 9326
store_p3 | p | 16397 | 0 | 0 | r | 16404 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9326 | | | |
| {200} | | {orientation=row,compression=no} | 9326
store_p11 | p | 16397 | 0 | 0 | r | 16402 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9326 | | | |
| {100} | | {orientation=row,compression=no} | 9326
store_p5 | p | 16397 | 0 | 0 | r | 16406 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9330 | | | |
| {300} | | {orientation=row,compression=no} | 9330
(6 rows)
7.删除分区表
drop table store;
DROP TABLE
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
