第八课课后作业打卡

• 分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。
• 分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。

1.创建分区表

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

create table store
(
        c1 int,
        c2 CHAR(2)
)
partition by range (c1)
(
	partition p01 values less than (100),
	partition p02 values less than (500),
	partition p03 values less than (1000),
	partition p04 values less than (5000),
	partition p05 values less than (10000)
);

insert into store values (30, 'd');
insert into store values (150, 'd');
insert into store values (300, 'd');
insert into store values (800, 'd');
insert into store values (1000, 'd');
insert into store values (3000, 'd');
insert into store values (8000, 'd');
insert into store values (9000, 'd');

超出分区范围报错：

omm=# insert into store values (30000, 'd');
ERROR:  inserted partition key does not map to any table partition
omm=# 

                           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 relname,boundaries from pg_partition;
 relname | boundaries 
---------+------------
 store   | 
 p01     | {100}
 p02     | {500}
 p03     | {1000}
 p04     | {5000}
 p05     | {10000}
(6 rows)

2.查看分区1上的数据

omm=# select * from store partition(p01);
(1 row)

omm=#  c1 | c2 
----+----
 30 | d 

3.重命名分区2

alter table store rename partition p02 to part2;

omm=# alter table store rename partition p02 to part2;
omm=# ALTER TABLE
omm=# select relname,boundaries from pg_partition;
 relname | boundaries 
---------+------------
 store   | 
 p01     | {100}
 p03     | {1000}
 p04     | {5000}
 p05     | {10000}
 part2   | {500}
(6 rows)

4.删除分区5

alter table store drop partition p05;

omm=# alter table store drop partition p05;
ALTER TABLE
omm=# select relname,boundaries from pg_partition;
 relname | boundaries 
---------+------------
 store   | 
 p01     | {100}
 p03     | {1000}
 p04     | {5000}
 part2   | {500}
(5 rows)

5.增加分区6

alter table store add partition p06 values less than (50000);
insert into store values (35000, ‘d’);
select * from store partition(p06);

omm=# alter table store add partition p06 values less than (50000);
ALTER TABLE
omm=# insert into store values (35000, 'd');
INSERT 0 1

select * from store partition(p06);
  c1   | c2 
-------+----
 35000 | d 
(1 row)

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

omm=# select relname,boundaries from pg_partition;
 relname | boundaries 
---------+------------
 store   | 
 p01     | {100}
 p03     | {1000}
 p04     | {5000}
 part2   | {500}
 p06     | {50000}
(6 rows)

7.删除分区表

–删除分区
alter table store drop partition p03;
–删除分区表
drop table store;

omm=# alter table store drop partition p03;
ALTER TABLE
omm=# 
omm=# select relname,boundaries from pg_partition;
 relname | boundaries 
---------+------------
 store   | 
 p01     | {100}
 p04     | {5000}
 part2   | {500}
 p06     | {50000}
(5 rows)

omm=# drop table store;
DROP TABLE
omm=# select relname,boundaries from pg_partition;
 relname | boundaries 
---------+------------
(0 rows)