PostgreSQL的分区表建立
PostgreSQL的分区表建立在数据库日渐庞大的时候,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。很多商业数据库都提供分区的概念,按不同的维度去存放数据,便于后期的管理,PG也不例外。下面是分区表创建步骤:www.zzzyk.com1.建立主表create table parent_table(id int,name character varying(20),create_time timestamp without time zone);2.建立子表,继承于主表create table parent_table_2012_01(check (create_time>=date '2012-01-01' and create_time<date '2012-02-01'))inherits(parent_table); www.zzzyk.comcreate table parent_table_2012_02(check (create_time>=date '2012-02-01' and create_time<date '2012-03-01'))inherits(parent_table);create table parent_table_2012_03(check (create_time>=date '2012-03-01' and create_time<date '2012-04-01'))inherits(parent_table);create table parent_table_2012_04(check (create_time>=date '2012-04-01' and create_time<date '2012-05-01'))inherits(parent_table);create table parent_table_2012_05(check (create_time>=date '2012-05-01' and create_time<date '2012-06-01'))inherits(parent_table);create table parent_table_2012_06(check (create_time>=date '2012-06-01' and create_time<date '2012-07-01'))inherits(parent_table);create table parent_table_2012_07(check (create_time>=date '2012-07-01' and create_time<date '2012-08-01'))inherits(parent_table);create table parent_table_2012_08(check (create_time>=date '2012-08-01' and create_time<date '2012-09-01'))inherits(parent_table);create table parent_table_2012_09(check (create_time>=date '2012-09-01' and create_time<date '2012-10-01'))inherits(parent_table);create table parent_table_2012_10(check (create_time>=date '2012-10-01' and create_time<date '2012-11-01'))inherits(parent_table); www.zzzyk.comcreate table parent_table_2012_11(check (create_time>=date '2012-11-01' and create_time<date '2012-12-01'))inherits(parent_table);create table parent_table_2012_12(check (create_time>=date '2012-12-01' and create_time<date '2013-01-01'))inherits(parent_table);3.创建触发器函数CREATE OR REPLACE FUNCTION test.tri_parent_tab_insert()RETURNS TRIGGER AS $$--author: kenyon--created:2012-05-24BEGINIF ( NEW.create_time >= DATE '2012-01-01' ANDNEW.create_time < DATE '2012-02-01' ) THENINSERT INTO test.parent_table_2012_01 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-02-01' ANDNEW.create_time < DATE '2012-03-01' ) THENINSERT INTO test.parent_table_2012_02 VALUES (NEW.id,NEW.name,NEW.create_time); www.zzzyk.comELSIF ( NEW.create_time >= DATE '2012-03-01' ANDNEW.create_time < DATE '2012-04-01' ) THENINSERT INTO test.parent_table_2012_03 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-04-01' ANDNEW.create_time < DATE '2012-05-01' ) THENINSERT INTO test.parent_table_2012_04 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-05-01' ANDNEW.create_time < DATE '2012-06-01' ) THENINSERT INTO test.parent_table_2012_05 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-06-01' ANDNEW.create_time < DATE '2012-07-01' ) THENINSERT INTO test.parent_table_2012_06 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-07-01' ANDNEW.create_time < DATE '2012-08-01' ) THENINSERT INTO test.parent_table_2012_07 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-08-01' ANDNEW.create_time < DATE '2012-09-01' ) THENINSERT INTO test.parent_table_2012_08 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-09-01' ANDNEW.create_time < DATE '2012-10-01' ) THENINSERT INTO test.parent_table_2012_09 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-10-01' ANDNEW.create_time < DATE '2012-11-01' ) THENINSERT INTO test.parent_table_2012_10 VALUES (NEW.id,NEW.name,NEW.create_time);ELSIF ( NEW.create_time >= DATE '2012-11-01' ANDNEW.create_time < DATE '2012-12-01' ) THENINSERT INTO test.parent_table_2012_11 VALUES (NEW.id,NEW.name,NEW.create_time); www.zzzyk.comELSIF ( NEW.create_time >= DATE '2012-12-01' AND&上一个:发掘数据库内存表的用处
下一个:mongodb常用管理命令
- 更多SQLServer疑问解答:
- 配置MSSQL复制指定快照文件夹提示:不是有效的路径或文件名
- 详细解读varchar和Nvarchar区别
- SQL SERVER 2005 同步复制技术
- 进程未能大容量复制到表 解决方法
- MSSql实例教程:MSSql数据库同步
- SQLServer2000同步复制技术实现(分发和订阅)
- sqlserver
- SQLSERVER
- 测试 sqlserver 最大用户数连接
- 写出一条Sql语句:取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键,注意:ID可能不是连续的
- sqlserver2008的安装问题。
- 为什么安装的SQL server 2008我的没有MSSQLSERVER协议啊?
- 怎样把exel表里的数据复制到sqlserver表里
- sqlserver数据库主键和外键问题
- sqlserver 中的左表连接查询和右表连接查询有啥不同?有什么用?