分区表实例

分区表

一、主表

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