postgresql 分区表

 

1.普通方式建立主表

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

2.创建分区表.(注意加上约束和继承)

create table tbl_partition_201211(
    check( join_date>=DATE '2012-11-01' and join_date< DATE '2012-12-01' )
)inherits(tbl_partition);

create table tbl_partition_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )      
) INHERITS (tbl_partition);

create table tbl_partition_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )      
) INHERITS (tbl_partition);

create table tbl_partition_201302 (
check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )  
) INHERITS (tbl_partition);

create table tbl_partition_201303 (
check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' ) 
) INHERITS (tbl_partition);

create table tbl_partition_201304 (
check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )  
) INHERITS (tbl_partition);

create table tbl_partition_201305 (
check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )  
) INHERITS (tbl_partition);

 

3.分区表上建立索引.

create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); 

create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);    

create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); 

create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); 

create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); 

create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); 

 

4.postgresql不能自动插入到字表.所以

要创建触发器函数和创建触发器.这样对外插入就不会有字表的感觉.

触发器函数:

CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.join_date >= DATE '2012-11-01' AND    
         NEW.join_date < DATE '2012-12-01' ) THEN  
        INSERT INTO tbl_partition_201211 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2012-12-01' AND  
            NEW.join_date < DATE '2013-01-01' ) THEN  
        INSERT INTO tbl_partition_201212 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-01-01' AND  
            NEW.join_date < DATE '2013-02-01' ) THEN  
        INSERT INTO tbl_partition_201301 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-02-01' AND  
            NEW.join_date < DATE '2013-03-01' ) THEN  
        INSERT INTO tbl_partition_201302 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-03-01' AND  
            NEW.join_date < DATE '2013-04-01' ) THEN  
        INSERT INTO tbl_partition_201303 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-04-01' AND  
            NEW.join_date < DATE '2013-05-01' ) THEN  
        INSERT INTO tbl_partition_201304 VALUES (NEW.*);
    ELSIF ( NEW.join_date >= DATE '2013-05-01' AND
            NEW.join_date < DATE '2013-06-01' ) THEN
        INSERT INTO tbl_partition_201305 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_traigger
    BEFORE INSERT ON tbl_partition
    For EACH ROW 
    EXECUTE PROCEDURE tbl_partition_insert_trigger();

 

 

 

5.插入数据进行测试.

 

insert into tbl_partition values (1, 'David', '1', '2013-01-10', 'TS');

insert into tbl_partition values (2, 'Sandy', '0', '2013-02-10', 'TS');

insert into tbl_partition values (3, 'Eagle', '1', '2012-11-01', 'TS');

insert into tbl_partition values (4, 'Miles', '1', '2012-12-15', 'SD');

insert into tbl_partition values (5, 'Simon', '1', '2012-12-10', 'SD');

insert into tbl_partition values (6, 'Rock', '1', '2012-11-10', 'SD');

insert into tbl_partition values (7, 'Peter', '1', '2013-01-11', 'SD');

insert into tbl_partition values (8, 'Sally', '0', '2013-03-10', 'BCSC');

insert into tbl_partition values (9, 'Carrie', '0', '2013-04-02', 'BCSC');

insert into tbl_partition values (10, 'Lee', '1', '2013-01-05', 'BMC');

insert into tbl_partition values (11, 'Nicole', '0', '2012-11-10', 'PROJ');

insert into tbl_partition values (12, 'Renee', '0', '2013-01-10', 'TS');

 

 

另: 解除分区方法:

alter table tbl_partition_201304 no inherit tbl_partition;

　　增加分区:

 

 

参考:

PostgreSQL官方说明：http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html

ITEYE：http://diegoball.iteye.com/blog/713826

kenyon(君羊)：http://my.oschina.net/Kenyon/blog/59455

博客园:http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html