Postgres主从分区表应用

1.实现分区表

（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）创建触发器

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

触发函数

CREATE OR REPLACE FUNCTION tbl_partition_inse