分区表
一、主表
billroute_for_test
CREATE TABLE billroute_for_test
(
bno character varying(12) NOT NULL, -- 运单号
deszno character varying(12), -- 识别网点
updatetime timestamp without time zone, -- 更新时间
CONSTRAINT pk_for_test PRIMARY KEY (bno )
)
WITH (
OIDS=FALSE
);
二、分区表
CREATE TABLE billroute_for_test_20120323
(
CONSTRAINT billroute_for_test_20120323_pk PRIMARY KEY (bno ),
CONSTRAINT billroute_for_test_20120323_updatetime_check CHECK (updatetime >= '2012-03-23 00:00:00'::timestamp without time zone AND updatetime < '2012-03-30 00:00:00'::timestamp without time zone)
)
INHERITS (billroute_for_test)
WITH (
OIDS=FALSE
);
CREATE TABLE billroute_for_test_20120401
(
CONSTRAINT billroute_for_test_20120401_pk PRIMARY KEY (bno ),
CONSTRAINT billroute_for_test_20120401_updatetime_check CHECK (updatetime >= '2012-04-01 00:00:00'::timestamp without time zone AND updatetime < '2012-04-15 00:00:00'::timestamp without time zone)
)
INHERITS (billroute_for_test)
WITH (
OIDS=FALSE
);
三、在主表创建规则
CREATE OR REPLACE RULE billroute_for_test_20120323 AS
ON INSERT TO billroute_for_test
WHERE new.updatetime >= '2012-03-23'::date AND new.updatetime < '2012-03-30'::date DO INSTEAD INSERT INTO billroute_for_test_20120323 (bno, deszno, updatetime)
VALUES (new.bno, new.deszno, new.updatetime);
CREATE OR REPLACE RULE billroute_for_test_20120401 AS
ON INSERT TO billroute_for_test
WHERE new.updatetime >= '2012-04-01'::date AND new.updatetime < '2012-04-15'::date DO INSTEAD INSERT INTO billroute_for_test_20120401 (bno, deszno, updatetime)
VALUES (new.bno, new.deszno, new.updatetime);
三、验证
插入数据
INSERT INTO billroute_for_test(
bno, deszno, updatetime)
VALUES ('00001','755w','2012-03-23 00:00:00');
INSERT INTO billroute_for_test(
bno, deszno, updatetime)
VALUES ('00002','755w','2012-04-10 00:00:00');
查询数据
select * from billroute_for_test_20120323
select * from billroute_for_test_20120401
select * from billroute_for_test