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=DATE2013-03-01 and logdate DATE2013-04-01) ) INH

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

) INHERITS(measurement);

create table measurement_201304(

    CHECK(logdate>=DATE'2013-04-01' and logdate

) INHERITS(measurement);

create table measurement_201305(

    CHECK(logdate>=DATE'2013-05-01' and logdate

) 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

            insert into measurement_201303 VALUES(NEW.*);

  ELSEIF(NEW.logdate >=date'2013-04-01' and NEW.logdate

        insert into measurement_201304 VALUES(NEW.*);

    ELSEIF(NEW.logdate >=date'2013-05-01' and NEW.logdate

            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;