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