postgresql创建分区
postgresql创建分区
1.创建主表
create table measurement(
city_id int not NULL,
logdate date not NULL,
peaktemp int,
unitsales int
);
2创建分区表
create table measurement_201303(
CHECK(logdate>=DATE'2013-03-01' and logdate< DATE'2013-04-01')
) INHERITS(measurement);
create table measurement_201304(
CHECK(logdate>=DATE'2013-04-01' and logdate< DATE'2013-05-01')
) INHERITS(measurement);
create table measurement_201305(
CHECK(logdate>=DATE'2013-05-01' and logdate< DATE'2013-06-01')
) INHERITS(measurement);
3,可以在相应的分区表上建立索引
create index measurement_201303_logdate on measurement_201303(logdate);
create index measurement_201304_logdate on measurement_201304(logdate);
create index measurement_201305_logdate on measurement_201305(logdate);
4.创建触发的存储过程
create or REPLACE FUNCTION measurement_insert_trigger()
returns trigger as $$
begin
if(NEW.logdate >=date'2013-03-01' and NEW.logdate <DATE'2013-04-01') THEN
insert into measurement_201303 VALUES(NEW.*);
ELSEIF(NEW.logdate >=date'2013-04-01' and NEW.logdate <DATE'2013-05-01') THEN
insert into measurement_201304 VALUES(NEW.*);
ELSEIF(NEW.logdate >=date'2013-05-01' and NEW.logdate <DATE'2013-06-01') THEN
insert into measurement_201305 VALUES(NEW.*);
ELSE
raise EXCEPTION 'Date out of range.Fix the measurment_insert_trigger() function!';
end if;
RETURN null;
end;
$$
LANGUAGE plpgsql;
5.创建触发器
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
6.插入数据
insert into measurement(city_id,logdate,peaktemp,unitsales) VALUES (1,'2013-03-02',1,1);
insert into measurement(city_id,logdate,peaktemp,unitsales) VALUES (2,'2013-04-02',2,2);
insert into measurement(city_id,logdate,peaktemp,unitsales) VALUES (3,'2013-05-02',3,3);
7.查询数据.
select *from measurement
select *from measurement_201303;
select *from measurement_201304;
select *from measurement_201305;
