关闭

分区表实例

460人阅读 评论(0) 收藏 举报

分区表

一、主表

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:22301次
    • 积分:402
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:6篇
    • 译文:0篇
    • 评论:1条
    文章分类
    最新评论