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;