目录
postgres通过partition做范围表分区
表分区是将一个表的数据拆分成多个物理表去存储,查询的时候合并成一个表查询。
首先确保当前的 postgres 版本支持表分区的,如果不支持,可以安装 pg_partman
扩展。
1、安装pg_partman
扩展
CREATE EXTENSION pg_partman;
2、创建需要分区表,按学生的入学时间分区
PARTITION BY RANGE
可以通过学生的入学时间 stu_enrol_date
按年去分区。
CREATE TABLE tb_student (
stu_id INT NOT NULL,
stu_name VARCHAR ( 100 ) NOT NULL,
stu_sex VARCHAR ( 2 ),
stu_age INT,
stu_grade VARCHAR ( 2 ),
stu_enrol_date DATE NOT NULL
) PARTITION BY RANGE ( stu_enrol_date );
3、创建分区
可以随时增加分区
create table tb_student_2020 partition of tb_student for VALUES from ('2020-01-01') to ('2020-12-31');
create table tb_student_2021 partition of tb_student for VALUES from ('2021-01-01') to ('2021-12-31');
create table tb_student_2022 partition of tb_student for VALUES from ('2022-01-01') to ('2022-12-31');
4、插入数据
插入的数据会自动根据 stu_enrol_date
放入到指定的分区表中
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (1,'小米','男',12,'6',to_date('2020-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (2,'小明','男',11,'6',to_date('2021-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (3,'小红','男',10,'5',to_date('2020-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (4,'小王','男',12,'6',to_date('2022-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (5,'小吴','男',10,'6',to_date('2021-03-01','yyyy-MM-dd'));
5、查询分区表
查询分区表时:合并查询
select * from tb_student;
结果显示全都是刚才插入的数据。
查询子分区表时:
select * from tb_student_2021;
显示stu_id为2、5的记录
6、不需要子分区时
可以指定某个子分区表删除,数据也会跟随一起删除。
drop table tb_student_2021;
7、直接插入子分区表时。
到直接 insert 到 tb_student_2020 表时,会去校验 stu_enrol_date 的约束,是否满足当前分区的结果。
正常:
insert into tb_student_2020(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (6,'小轩','男',12,'6',to_date('2020-03-01','yyyy-MM-dd'));
不满足时:
insert into tb_student_2020(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (7,'小轩','男',12,'6',to_date('2021-03-01','yyyy-MM-dd'));
报错:ERROR: new row for relation "tb_student_2020" violates partition constraint DETAIL: Failing row contains (7, 小轩, 男, 12, 6, 2021-03-01).
8、navicat可以查看到分区的表与分区的维度
找到 tb_student
,点击设计表,点击分区就可以查看到分区的详情信息了。