PostgreSQL分区表（Table Partitioning）

1、创建主表

CREATE TABLE t21pgPart(imp_date BIGINT,c1 BIGINT,c2 BIGINT);

2、创建分区表

CREATE TABLE t21pgPart_p_20150901(CHECK (imp_date >= 20150901 )) INHERITS (t21pgPart);
CREATE TABLE t21pgPart_p_201508280901(CHECK (imp_date < 20150901 AND imp_date > 20150828)) INHERITS (t21pgPart);
CREATE TABLE t21pgPart_p_20150828(CHECK (imp_date <= 20150828)) INHERITS (t21pgPart);

3、定义分区RULE

CREATE OR REPLACE RULE insert_t21pgPart_p_20150901
AS ON INSERT TO t21pgPart 
       WHERE imp_date >= 20150901
       DO INSTEAD
       INSERT INTO t21pgPart_p_20150901 VALUES(NEW.*);


CREATE OR REPLACE RULE insert_t21pgPart_p_201508280901
AS ON INSERT TO t21pgPart 
       WHERE imp_date < 20150901 AND imp_date > 20150828
       DO INSTEAD
       INSERT INTO t21pgPart_p_201508280901 VALUES(NEW.*);


CREATE OR REPLACE RULE insert_t21pgPart_p_20150828
AS ON INSERT TO t21pgPart 
       WHERE imp_date <= 20150828
       DO INSTEAD
       INSERT INTO t21pgPart_p_20150828 VALUES(NEW.*);

4、插入测试数据

INSERT INTO t21pgPart(imp_date,c1,c2) VALUES(20150901,11,12);
INSERT INTO t21pgPart(imp_date,c2) VALUES(20150909,21,22);
INSERT INTO t21pgPart(imp_date,c2) VALUES(20150831,31,32);
INSERT INTO t21pgPart(imp_date,c2) VALUES(20150829,41,42);
INSERT INTO t21pgPart(imp_date,c2) VALUES(20150828,51,52);
INSERT INTO t21pgPart(imp_date,c2) VALUES(20150809,61,62);

5、查看数据分布情况，是否分布到了正确的分区表

SELECT p.relname,t.tableoid,t.* FROM t21pgPart t,pg_class p WHERE t.tableoid = p.oid;

6、其他

6.1、删除主表（级联删除分区表）

drop table t21pgPart CASCADE;

6.2、Rule是一个分流的办法，还有TRIGGER也能做到让正确的数据流向正确的分区子表。

6.3、删除表，注意使用CASCADE

david=# drop table t21pgPart_p_20150831;
ERROR:  cannot drop table t21pgpart_p_20150831 because other objects depend on it
DETAIL:  rule insert_t21pgpart_p_20150831 on table t21pgpart depends on table t21pgpart_p_20150831
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
david=# drop table t21pgPart_p_20150831 CASCADE;
NOTICE:  drop cascades to rule insert_t21pgpart_p_20150831 on table t21pgpart
DROP TABLE

7、参考url： http://blog.chinaunix.net/uid-24774106-id-3887099.html

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

PostgreSQL分区表（Table Partitioning）

猜你在找的Postgre SQL相关文章