欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

postgresql创建分区

程序员文章站 2022-06-17 12:55:34
...

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;