postgresql分区表2

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了postgresql分区表2，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
  1. 1). 创建"主表"，所有分区都从它继承。
  2. create table ft
  3. (            
  4. sid      int    not null,sdate     date  not null,saddress  varchar(100)
  5. )tablespace tb01;  
  6.  
  7. 2). 创建几个"子"表，每个都从主表上继承
  8.  
  9. create table ft01 
  10. (check ( sdate >= date '2015-01-01' and sdate < date '2015-02-01') ) 
  11. inherits (ft) 
  12. tablespace tb01;
  13.  
  14.  
  15. create table ft02 
  16. ( check ( sdate >= date '2015-02-01' and sdate < date '2015-03-01'))
  17. inherits (ft)
  18. tablespace tb01;
  19.  
  20.  
  21. create table ft03
  22. ( check ( sdate >= date '2015-03-01' and sdate < date '2015-04-01'))
  23. inherits (ft)
  24. tablespace tb01;
  25.  
  26. create table ft04 
  27. ( check ( sdate >= date '2015-04-01' and sdate < date '2015-05-01'))
  28. inherits (ft)
  29. tablespace tb01;
  30.  
  31.  
  32. create table ft05
  33. ( check ( sdate >= date '2015-05-01' and sdate < date '2015-06-01'))
  34. inherits (ft)
  35. tablespace tb01;
  36.  
  37. create table ft06 
  38. ( check ( sdate >= date '2015-06-01' and sdate < date '2015-07-01'))
  39. inherits (ft)
  40. tablespace tb01;
  41.  
  42.  
  43. create table ft07 
  44. ( check ( sdate >= date '2015-07-01' and sdate < date '2015-08-01'))
  45. inherits (ft)
  46. tablespace tb01;
  47.  
  48. create table ft08
  49. ( check ( sdate >= date '2015-08-01' and sdate < date '2015-09-01'))
  50. inherits (ft)
  51. tablespace tb01;
  52.  
  53. create table ft09 
  54. ( check ( sdate >= date '2015-09-01' and sdate < date '2015-10-01'))
  55. inherits (ft)
  56. tablespace tb01;
  57.  
  58.  
  59. create table ft10
  60. ( check ( sdate >= date '2015-10-01' and sdate < date '2015-11-01'))
  61. inherits (ft)
  62. tablespace tb01;
  63.  
  64.  
  65. create table ft11
  66. ( check ( sdate >= date '2015-11-01' and sdate < date '2015-12-01'))
  67. inherits (ft)
  68. tablespace tb01;
  69.  
  70.  
  71. create table ft12
  72. ( check ( sdate >= date '2015-12-01' and sdate < date '2016-1-01'))
  73. inherits (ft)
  74. tablespace tb01;
  75.  
  76.  
  77. create index index_ft01_sdate on ft01 (sdate) tablespace tb01_index;
  78. create index index_ft02_sdate on ft02 (sdate) tablespace tb01_index;
  79. create index index_ft03_sdate on ft03 (sdate) tablespace tb01_index ;
  80. create index index_ft04_sdate on ft04 (sdate) tablespace tb01_index;
  81. create index index_ft05_sdate on ft05 (sdate) tablespace tb01_index;
  82. create index index_ft06_sdate on ft06 (sdate) tablespace tb01_index;
  83. create index index_ft07_sdate on ft07 (sdate) tablespace tb01_index;
  84. create index index_ft08_sdate on ft08 (sdate) tablespace tb01_index;
  85. create index index_ft09_sdate on ft09 (sdate) tablespace tb01_index;
  86. create index index_ft10_sdate on ft10 (sdate) tablespace tb01_index;
  87. create index index_ft11_sdate on ft11 (sdate) tablespace tb01_index;
  88. create index index_ft12_sdate on ft12 (sdate) tablespace tb01_index;
  89.  
  90. create or replace function ft_insert_trigger() 
  91.  returns trigger as $$ 
  92.  begin
  93.      if( new.sdate between '2015-1-01' and '2015-02-01' ) then 
  94.          insert into ft01 values (new.*); 
  95.      elsif( new.sdate between '2015-2-01' and '2015-03-01' ) then 
  96.          insert into ft02 values (new.*); 
  97.      elsif( new.sdate between '2015-3-01' and '2015-04-01' ) then 
  98.          insert into ft03 values (new.*); 
  99.      elsif( new.sdate between '2015-4-01' and '2015-05-01' ) then 
  100.          insert into ft04 values (new.*); 
  101.      elsif( new.sdate between '2015-05-01' and '2015-06-01' ) then 
  102.          insert into ft05 values (new.*); 
  103.      elsif( new.sdate between '2015-06-01' and '2015-07-01' ) then 
  104.          insert into ft06 values (new.*);
  105.      elsif( new.sdate between '2015-07-01' and '2015-08-01' ) then 
  106.          insert into ft07 values (new.*);
  107.      elsif( new.sdate between '2015-08-01' and '2015-09-01' ) then 
  108.          insert into ft08 values (new.*);
  109.      elsif( new.sdate between '2015-09-01' and '2015-10-01' ) then 
  110.          insert into ft09 values (new.*);
  111.      elsif( new.sdate between '2015-10-01' and '2015-11-01' ) then 
  112.          insert into ft10 values (new.*);
  113.      elsif( new.sdate between '2015-11-01' and '2015-12-01' ) then 
  114.          insert into ft11 values (new.*);
  115.      elsif( new.sdate between '2015-12-01' and '2016-01-01' ) then 
  116.          insert into ft12 values (new.*);
  117.      else 
  118.          raise exception '!out of range!'; 
  119.      end if; 
  120.      return null; 
  121.  end; 
  122.  $$ 
  123.  language plpgsql;
  124.  
  125.  
  126. create trigger insert_ft_trigger 
  127. before insert on ft 
  128. for each row execute procedure ft_insert_trigger() ;
  129.  
  130.  
  131. ----插入测试数据
  132.  
  133. insert into ft(sid,sdate,saddress) select n,'2015-01-03',n||':杨楂文'
  134. from generate_series(1,100) n;
  135.  
  136. insert into ft(sid,'2015-02-04','2015-03-05','2015-04-04','2015-05-05','2015-06-06','2015-07-07','2015-08-08','2015-09-09','2015-10-10','2015-11-11','2015-12-12',100) n;
  137.  
  138.  
  139. ----查询
  140.  
  141.  SET constraint_exclusion = off;
  142.  #EXPLAIN SELECT count(*) FROM ft WHERE sdate >= DATE '2015-01-01';  
  143.  
  144.                              QUERY PLAN                            
  145. ------------------------------------------------------------------
  146.  Aggregate  (cost=30.02..30.03 rows=1 width=0)
  147.    ->  Append  (cost=0.00..27.01 rows=1202 width=0)
  148.          ->  Seq Scan on ft  (cost=0.00..0.00 rows=1 width=0)
  149.                Filter: (sdate >= '2015-01-01'::date)
  150.          ->  Seq Scan on ft01  (cost=0.00..2.26 rows=101 width=0)
  151.                Filter: (sdate >= '2015-01-01'::date)
  152.          ->  Seq Scan on ft02  (cost=0.00..2.25 rows=100 width=0)
  153.                Filter: (sdate >= '2015-01-01'::date)
  154.          ->  Seq Scan on ft03  (cost=0.00..2.25 rows=100 width=0)
  155.                Filter: (sdate >= '2015-01-01'::date)
  156.          ->  Seq Scan on ft04  (cost=0.00..2.25 rows=100 width=0)
  157.                Filter: (sdate >= '2015-01-01'::date)
  158.          ->  Seq Scan on ft05  (cost=0.00..2.25 rows=100 width=0)
  159.                Filter: (sdate >= '2015-01-01'::date)
  160.          ->  Seq Scan on ft06  (cost=0.00..2.25 rows=100 width=0)
  161.                Filter: (sdate >= '2015-01-01'::date)
  162.          ->  Seq Scan on ft07  (cost=0.00..2.25 rows=100 width=0)
  163.                Filter: (sdate >= '2015-01-01'::date)
  164.          ->  Seq Scan on ft08  (cost=0.00..2.25 rows=100 width=0)
  165.                Filter: (sdate >= '2015-01-01'::date)
  166.          ->  Seq Scan on ft09  (cost=0.00..2.25 rows=100 width=0)
  167.                Filter: (sdate >= '2015-01-01'::date)
  168.          ->  Seq Scan on ft10  (cost=0.00..2.25 rows=100 width=0)
  169.                Filter: (sdate >= '2015-01-01'::date)
  170.          ->  Seq Scan on ft11  (cost=0.00..2.25 rows=100 width=0)
  171.                Filter: (sdate >= '2015-01-01'::date)
  172.          ->  Seq Scan on ft12  (cost=0.00..2.25 rows=100 width=0)
  173.                Filter: (sdate >= '2015-01-01'::date)
  174. (28 rows)
  175.  
  176.  
  177.  
  178.  SET constraint_exclusion = off;
  179.  #EXPLAIN SELECT count(*) FROM ft WHERE sdate >= DATE '2015-01-01';  
  180.  ------------------------------------------------------------------
  181.  Aggregate  (cost=30.02..30.03 rows=1 width=0)
  182.    ->  Append  (cost=0.00..27.01 rows=1202 width=0)
  183.          ->  Seq Scan on ft  (cost=0.00..0.00 rows=1 width=0)
  184.                Filter: (sdate >= '2015-01-01'::date)
  185.          ->  Seq Scan on ft01  (cost=0.00..2.26 rows=101 width=0)
  186.                Filter: (sdate >= '2015-01-01'::date)
  187.          ->  Seq Scan on ft02  (cost=0.00..2.25 rows=100 width=0)
  188.                Filter: (sdate >= '2015-01-01'::date)
  189.          ->  Seq Scan on ft03  (cost=0.00..2.25 rows=100 width=0)
  190.                Filter: (sdate >= '2015-01-01'::date)
  191.          ->  Seq Scan on ft04  (cost=0.00..2.25 rows=100 width=0)
  192.                Filter: (sdate >= '2015-01-01'::date)
  193.          ->  Seq Scan on ft05  (cost=0.00..2.25 rows=100 width=0)
  194.                Filter: (sdate >= '2015-01-01'::date)
  195.          ->  Seq Scan on ft06  (cost=0.00..2.25 rows=100 width=0)
  196.                Filter: (sdate >= '2015-01-01'::date)
  197.          ->  Seq Scan on ft07  (cost=0.00..2.25 rows=100 width=0)
  198.                Filter: (sdate >= '2015-01-01'::date)
  199.          ->  Seq Scan on ft08  (cost=0.00..2.25 rows=100 width=0)
  200.                Filter: (sdate >= '2015-01-01'::date)
  201.          ->  Seq Scan on ft09  (cost=0.00..2.25 rows=100 width=0)
  202.                Filter: (sdate >= '2015-01-01'::date)
  203.          ->  Seq Scan on ft10  (cost=0.00..2.25 rows=100 width=0)
  204.                Filter: (sdate >= '2015-01-01'::date)
  205.          ->  Seq Scan on ft11  (cost=0.00..2.25 rows=100 width=0)
  206.                Filter: (sdate >= '2015-01-01'::date)
  207.          ->  Seq Scan on ft12  (cost=0.00..2.25 rows=100 width=0)
  208.                Filter: (sdate >= '2015-01-01'::date)
  209. (28 rows)
  210.  
  211.  
  212.  
  213. # EXPLAIN SELECT count(*) FROM ft WHERE sdate between '2015-03-01' and '2015-04-01'; 
  214.                                        QUERY PLAN                                        
  215. -----------------------------------------------------------------------------------------
  216.  Aggregate  (cost=5.25..5.26 rows=1 width=0)
  217.    ->  Append  (cost=0.00..5.00 rows=102 width=0)
  218.          ->  Seq Scan on ft  (cost=0.00..0.00 rows=1 width=0)
  219.                Filter: ((sdate >= '2015-03-01'::date) AND (sdate <= '2015-04-01'::date))
  220.          ->  Seq Scan on ft03  (cost=0.00..2.50 rows=100 width=0)
  221.                Filter: ((sdate >= '2015-03-01'::date) AND (sdate <= '2015-04-01'::date))
  222.          ->  Seq Scan on ft04  (cost=0.00..2.50 rows=1 width=0)
  223.                Filter: ((sdate >= '2015-03-01'::date) AND (sdate <= '2015-04-01'::date))
  224. (8 rows)
  225.  

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问