写在前面:这不是一个知识系列博客,这只是对本人使用过知识的一个记录,最好的资源应该是官方文档。
官网doc: https://www.postgresql.org/docs/current/static/plpgsql.html
-- 测试结果为不如用pg继承实现的分区
-- 表分区 关键字(PARTITION BY) 分区方法(RANGE/list) 分区key(logdate)
-- Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
-- 指定表空间
CREATE TABLE measurement_y2018m02 PARTITION OF measurement
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01')
TABLESPACE pg_default;
CREATE TABLE measurement_y2018m03 PARTITION OF measurement
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01')
PARTITION BY RANGE (peaktemp);
-- 取消分区表与子表关系
ALTER TABLE measurement DETACH PARTITION measurement_y2018m02;
-- 添加分区表 方式一灵活,方式二简单
-- 方式一:
CREATE TABLE measurement_y2018m02(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
)
ALTER TABLE measurement_y2018m02 ADD CONSTRAINT y2018m02 -- 非必须操作。加了会免去添加分区时扫描分区验证
CHECK ( logdate >= DATE '2018-02-01' AND logdate < DATE '2018-03-01' );
ALTER TABLE measurement ATTACH PARTITION measurement_y2018m02
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01' );
-- 方式二
CREATE TABLE measurement_y2018m02 PARTITION OF measurement
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01')
TABLESPACE pg_default;
-- 创建表空间
create tablespace fast_tb location 'D:/environment/pgsql-10.0-1/newdata/mytablespace';