作业：
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
omm=# create table store
omm-# (
omm(# c1 int,
omm(# c2 CHAR(2)
omm(# )
omm-# partition by range (c1)
omm-# (
omm(# partition p1 values less than (50),
omm(# partition p2 values less than (100),
omm(# partition p3 values less than (150),
omm(# partition p4 values less than (200),
omm(# partition p5 values less than (250)
omm(# );
CREATE TABLE
omm=# \d store
Table "public.store"
Column | Type | Modifiers
--------+--------------+-----------
c1 | integer |
c2 | character(2) |
Range partition by(c1)
Number of partition: 5 (View pg_partition to check each partition range.)
omm=# insert into store values (10, 'a'), (80, 'b'), (140, 'c'), (180, 'c'), (240, 'c');
INSERT 0 5
2.查看分区1上的数据
omm=#
omm=# select * from store partition(p1);
c1 | c2
----+----
10 | a
(1 row)
3.重命名分区2
omm=# alter table store rename partition p2 to p22;
ALTER TABLE
4.删除分区5
omm=# alter table store drop partition p5;
ALTER TABLE
5.增加分区6
omm=# alter table store add partition p6 values less than (300);
ALTER TABLE
6.在系统表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 | relc
udescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | relop
tions | relfrozenxid64
---------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+-
--------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+-----
---------+--------------+----------+---------+--------------------+----------+------------+---------+-------------------------
--------------------------+----------------
store | r | 16397 | 0 | 0 | r | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,compres
sion=no,wait_clean_gpi=n} | 0
p1 | p | 16397 | 0 | 0 | r | 16401 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9326 | | | | | {50} | | {orientation=row,compres
sion=no} | 9326
p3 | p | 16397 | 0 | 0 | r | 16403 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9326 | | | | | {150} | | {orientation=row,compres
sion=no} | 9326
p4 | p | 16397 | 0 | 0 | r | 16404 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9326 | | | | | {200} | | {orientation=row,compres
sion=no} | 9326
p22 | p | 16397 | 0 | 0 | r | 16402 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9326 | | | | | {100} | | {orientation=row,compres
sion=no} | 9326
p6 | p | 16397 | 0 | 0 | r | 16406 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9330 | | | | | {300} | | {orientation=row,compres
sion=no} | 9330
(6 rows)
7.删除分区表
omm=# drop table store;
DROP TABLE
