PostgreSQL分区表

1，  创建分区表

create table partitioned

(

       dt date,

       a int

);

 

2，  创建分区表

create table p20090101 (

check(dt >= DATE ‘2009-01-01’ AND dt < DATE ‘2009-01-02’)

) inherits(partitioned);

 

create table p20090102 (

check(dt >= DATE ‘2009-01-02’ AND dt < DATE ‘2009-01-03’)

) inherits(partitioned);

 

 

3，  创建规则

CREATE OR REPLACE RULE partitioned_p20090101 AS
ON INSERT TO partitioned WHERE
      (dt >= DATE ‘2009-01-01’ AND dt < DATE ‘2009-01-02’)

DO INSTEAD
         INSERT INTO p20090101 VALUES (NEW.dt,

NEW.a );

 

CREATE OR REPLACE RULE partitioned_p20090102 AS
ON INSERT TO partitioned WHERE
      (dt >= DATE ‘2009-01-02’ AND logdate < DATE ‘2009-01-03’)

DO INSTEAD
         INSERT INTO p20090102 VALUES (NEW.dt,

NEW.a )

 

4，  打开分区消除

SET constraint_exclusion = on;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14293419/viewspace-613279/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14293419/viewspace-613279/