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;