pgSql分区表新建及过程（BBB,ZJLJB）

--创建主表

CREATE TABLE zero_main (            --主表

        city_id int    NOT NULL,

        logdate date  NOT NULL,

        peaktemp int

    );   

--创建几个分区表

    CREATE TABLE zero_main_201710 ( ) INHERITS (zero_main);

    CREATE TABLE zero_main_201711 ( ) INHERITS (zero_main);

    CREATE TABLE zero_main_201712 ( ) INHERITS (zero_main);

    CREATE TABLE zero_main_201801 ( ) INHERITS (zero_main);

    CREATE TABLE zero_main_201802 ( ) INHERITS (zero_main);

    CREATE TABLE zero_main_201803 ( ) INHERITS (zero_main);

--创建几个分区表（以时间logdate为约束条件）

     CREATE TABLE zero_main_201710 (

        CHECK ( logdate >= DATE '2017-10-01' AND logdate < DATE '2017-11-01')

    ) INHERITS (zero_main);

    CREATE TABLE zero_main_201711 (

        CHECK (logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01')

    ) INHERITS (zero_main);

    CREATE TABLE zero_main_201712 (

        CHECK (logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01')

    ) INHERITS (zero_main);

    CREATE TABLE zero_main_201801 (

        CHECK (logdate >= DATE '2018-01-01' AND logdate < DATE '2018-02-01')

    ) INHERITS (zero_main);

    CREATE TABLE zero_main_201802 (

        CHECK (logdate >= DATE '2018-02-01' AND logdate < DATE '2018-03-01')

    ) INHERITS (zero_main);

     CREATE TABLE zero_main_201803 (

        CHECK (logdate >= DATE '2018-03-01' AND logdate < DATE '2018-04-01')

    ) INHERITS (zero_main);    

--创建索引

    CREATE INDEX zero_main_201710_logdate ON zero_main_201710 (logdate);

    CREATE INDEX zero_main_201711_logdate ON zero_main_201711 (logdate);

    CREATE INDEX zero_main_201712_logdate ON zero_main_201712 (logdate);

    CREATE INDEX zero_main_201801_logdate ON zero_main_201801 (logdate);

    CREATE INDEX zero_main_201802_logdate ON zero_main_201802 (logdate);

    CREATE INDEX zero_main_201803_logdate ON zero_main_201803 (logdate);   

--定义一个规则或者触发器，把对主表的修改重定向到适当的分区表。

    CREATE RULE zero_main_insert_201710 AS

    ON INSERT TO zero_main WHERE (logdate >= DATE '2017-10-01' AND logdate < DATE '2017-11-01')

    DO INSTEAD

    INSERT INTO zero_main_201710 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    

    CREATE RULE zero_main_insert_201711 AS

    ON INSERT TO zero_main WHERE (logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01')

    DO INSTEAD

    INSERT INTO zero_main_201711 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    

    CREATE RULE zero_main_insert_201712 AS

    ON INSERT TO zero_main WHERE (logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01')

    DO INSTEAD

    INSERT INTO zero_main_201712 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    CREATE RULE zero_main_insert_201801 AS

    ON INSERT TO zero_main WHERE (logdate >= DATE '2018-01-01' AND logdate < DATE '2018-02-01')

    DO INSTEAD

    INSERT INTO zero_main_201801 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    CREATE RULE zero_main_insert_201802 AS

    ON INSERT TO zero_main WHERE (logdate >= DATE '2018-02-01' AND logdate < DATE '2018-03-01')

    DO INSTEAD

    INSERT INTO zero_main_201802 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    CREATE RULE zero_main_insert_201803 AS

    ON INSERT TO zero_main WHERE (logdate >= DATE '2018-03-01' AND logdate < DATE '2018-04-01')

    DO INSTEAD

    INSERT INTO zero_main_201803 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    

   --创建新分区

     /* 创建一个独立的数据表(zero_main_201804)，该表在创建时以将来的主表(zero_main)为模板，包含模板表的缺省值(DEFAULTS)和一致性约束(CONSTRAINTS)。*/

      CREATE TABLE zero_main_201804

        (LIKE zero_main INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

     /* 为该表创建未来作为子表时需要使用的检查约束。*/

      ALTER TABLE zero_main_201804 ADD CONSTRAINT 201804

        CHECK (logdate >= DATE '2018-04-01' AND logdate < DATE '2018-05-01');

     /* 导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后，如有可能，还可以做进一步的数据处理，如数据转换、过滤等。*/

       \copy measurement_y2008m02 from 'measurement_y2008m02'

     /* 在适当的时候，或者说在需要的时候，让该表继承主表。*/

      ALTER TABLE measurement_y2008m02 INHERIT measurement;

--约束排除

     你可以使用EXPLAIN命令显示一个规划在constraint_exclusion打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：    

    SET constraint_exclusion = off;

    EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

       SET constraint_exclusion = on;

    EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';      

--测试

INSERT INTO public.zero_main(

            city_id, logdate, peaktemp)

    VALUES (1, '2017-11-13 12:13:11', 2);

    INSERT INTO public.zero_main(

            city_id, logdate, peaktemp)

    VALUES (2, '2017-12-13 12:13:11', 3);

    

    INSERT INTO public.zero_main(

            city_id, logdate, peaktemp)

    VALUES (3, '2018-01-13 12:13:11', 4);

    

    INSERT INTO public.zero_main(

            city_id, logdate, peaktemp)

    VALUES (4, '2018-02-13 12:13:11', 5);

    INSERT INTO public.zero_main(

            city_id, logdate, peaktemp)

    VALUES (4, '2017-09-13 12:13:11', 5);

    INSERT INTO public.zero_main(

            city_id, logdate, peaktemp)

    VALUES (4, '2018-05-13 12:13:11', 5);

select * from zero_main where logdate >= DATE '2018-02-01' and logdate < DATE '2018-03-01'

SET constraint_exclusion = off;

    EXPLAIN SELECT count(*) FROM zero_main WHERE logdate >= DATE '2006-01-01';    

     SET constraint_exclusion = on;

    EXPLAIN SELECT count(*) FROM zero_main WHERE logdate >= DATE '2006-01-01';