omm=# omm-# (
omm(# create table update_table
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
omm=#

–查看分区表信息

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

omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | relt
uples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescreli
d | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----
------+---------------+---------------+---------------+------------+-------------+---------------+-------------+--------------
--+--------------+--------------+----------+---------+--------------------+----------+------------+---------+-----------------
----------------------------------+----------------
update_table | r | 16442 | 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 | 16442 | 0 | 0 | r | 16446 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9365 | | | | | {50} | | {orientation=row
,compression=no} | 9365
update_table_p1 | p | 16442 | 0 | 0 | r | 16447 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
update_table_p2 | p | 16442 | 0 | 0 | r | 16448 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9365 | | | | | {100} | | {orientation=row
,compression=no} | 9365
0 | 0 | 9365 | | | | | {150} | | {orientation=row
,compression=no} | 9365
(4 rows)

–插入数据

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

–查看各区上的数据

omm=# select * from update_table partition(update_table_p0);
c1 | c2
----+----
1 | a
(1 row)

2.创建分区

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
omm=# 

3.修改分区属性

omm=# alter table update_table rename partition update_table_p1 to update_table_p1_1;
ALTER TABLE

4.删除分区

omm=# alter table update_table drop partition update_table_p0;

5.删除分区表

omm=# drop table update_table;

DROP TABLE
omm=# 

操作截图：

课程作业

1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录

omm=# create table store
omm-# (
omm(# c1 int,
omm(# c2 CHAR(2)
omm(# )
omm-# partition by range (c1)
omm-# (
omm(# partition store_p0 values less than (5),
omm(# partition store_p1 values less than (10),
omm(# partition store_p2 values less than (15),
omm(# partition store_p3 values less than (20),
omm(# partition store_p4 values less than (25)
omm(# );
CREATE TABLE

omm=# insert into store values (1, 'a'), (8, 'b'), (13, 'c'),(17, 'd'),(22, 'e');
INSERT 0 5

2.查看分区1上的数据

omm=# select * from store partition(store_p0);
omm=# c1 | c2
----+----
1 | a
(1 row)

3.重命名分区2

omm=# alter table store rename partition store_p1 to store_p1new;
ALTER TABLE

4.删除分区5

omm=# alter table store drop partition store_p4;
ALTER TABLE

5.增加分区6

omm=# alter table store add partition store_p5 values less than (30);
ALTER TABLE

6.在系统表pg_partition中查看分区信息

omm=# \d+ store;
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
Range partition by(c1)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuple
s | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid |
relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | r
eloptions | relfrozenxid64
-------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+---------
--+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+-
-------------+--------------+----------+---------+--------------------+----------+------------+---------+---------------------
------------------------------+----------------
store | r | 16450 | 0 | 0 | r | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,com
pression=no,wait_clean_gpi=n} | 0
store_p0 | p | 16450 | 0 | 0 | r | 16454 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {5} | | {orientation=row,com
pression=no} | 9374
store_p2 | p | 16450 | 0 | 0 | r | 16456 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {15} | | {orientation=row,com
pression=no} | 9374
store_p3 | p | 16450 | 0 | 0 | r | 16457 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {20} | | {orientation=row,com
pression=no} | 9374
store_p1new | p | 16450 | 0 | 0 | r | 16455 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {10} | | {orientation=row,com
pression=no} | 9374
store_p5 | p | 16450 | 0 | 0 | r | 16459 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9378 | | | | | {30} | | {orientation=row,com
pression=no} | 9378
(6 rows)

7.删除分区表

omm=# drop table store;
DROP TABLE

操作截图：