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;
上一篇: 【数据挖掘导论】绪论
下一篇: 多表联系关系调用
推荐阅读
-
Linux 常见 RAID 及软 RAID 创建
-
创建基于ASP.NET core 3.1 的RazorPagesMovie项目(一)-创建和使用默认的模板
-
创建基于ASP.NET core 3.1 的RazorPagesMovie项目(三)-已搭建基架的Razor页面解释和更新
-
带你使用Visual Studio 2019创建一个MVC Web应用
-
eclipse创建springboot项目
-
创建简单spring boot项目
-
浅析php创建者模式
-
链接 Mysql 创建 数据库和创表,增加数据
-
JavaScript 批量创建数组的方法
-
详解Centos/Linux下调整分区大小(以home和根分区为例)