postgresql 分区表

create table tbl_partition (
 id integer,
 name varchar(20),
 gender boolean,
 join_date date,
 dept char(4));


create table tbl_partition_201801 (
check ( join_date >= DATE '2018-01-01' AND join_date < DATE '2018-01-31' )       
) INHERITS (tbl_partition);


 create table tbl_partition_201802 (
check ( join_date >= DATE '2018-02-01' AND join_date < DATE '2018-02-28' )      
) INHERITS (tbl_partition);


 create table tbl_partition_201803 (
check ( join_date >= DATE '2018-03-01' AND join_date < DATE '2018-03-31' )      
) INHERITS (tbl_partition);




create index tbl_partition_201801_joindate on tbl_partition_201801 (join_date);
create index tbl_partition_201802_joindate on tbl_partition_201802 (join_date);
create index tbl_partition_201803_joindate on tbl_partition_201803 (join_date);

CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.join_date >= DATE '2018-01-01' AND    
         NEW.join_date < DATE '2018-01-31' ) THEN  
        INSERT INTO tbl_partition_201801 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2018-02-01' AND  
            NEW.join_date < DATE '2018-02-28' ) THEN  
        INSERT INTO tbl_partition_201802 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2018-03-01' AND  
            NEW.join_date < DATE '2018-03-31' ) THEN  
        INSERT INTO tbl_partition_201803 VALUES (NEW.*);  
    
    ELSE  
        RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
    END IF;  
    RETURN NULL;  
END;  
$$  

LANGUAGE plpgsql;

CREATE TRIGGER insert_tbl_partition_trigger
 BEFORE INSERT ON tbl_partition

FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();

set constraint_exclusion = off;  

set constraint_exclusion = on; 

explain select count(*) from tbl_partition where join_date >= DATE '2018-03-01';