PostgreSQL分区表（Table Partitioning）

来源：互联网发布：怎么没人去淘宝打假编辑：程序博客网时间：2024/06/11 04:53

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_20150901AS ON INSERT TO t21pgPart        WHERE imp_date >= 20150901       DO INSTEAD       INSERT INTO t21pgPart_p_20150901 VALUES(NEW.*);CREATE OR REPLACE RULE insert_t21pgPart_p_201508280901AS 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_20150828AS 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,c1,c2) VALUES(20150909,21,22);INSERT INTO t21pgPart(imp_date,c1,c2) VALUES(20150831,31,32);INSERT INTO t21pgPart(imp_date,c1,c2) VALUES(20150829,41,42);INSERT INTO t21pgPart(imp_date,c1,c2) VALUES(20150828,51,52);INSERT INTO t21pgPart(imp_date,c1,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 itDETAIL:  rule insert_t21pgpart_p_20150831 on table t21pgpart depends on table t21pgpart_p_20150831HINT:  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 t21pgpartDROP TABLE

7、参考url：

http://blog.chinaunix.net/uid-24774106-id-3887099.html

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

0 0