【PostgreSQL】分区表

创建销售明细表，作为“父表” create table sales_detail ( product_id int not null, price numeric(12,2), amount int not null, sale_date date not null, buyer varchar(40), buyer_contact text ); --根据销售日期sale_date字段，每个季度作为一个分区，创建分区表 create table sales_detail_Y2017Q01(check (sale_date >= date ‘2017-01-01‘ and sale_date < date ‘2017-04-01‘) ) inherits (sales_detail); create table sales_detail_Y2017Q02(check (sale_date >= date ‘2017-04-01‘ and sale_date < date ‘2017-07-01‘) ) inherits (sales_detail); create table sales_detail_Y2017Q03(check (sale_date >= date ‘2017-07-01‘ and sale_date < date ‘2017-10-01‘) ) inherits (sales_detail); create table sales_detail_Y2017Q04(check (sale_date >= date ‘2017-10-01‘ and sale_date < date ‘2018-01-01‘) ) inherits (sales_detail); --在分区键sale_detail上创建索引 create index sales_detail_Y2017Q01_sale_date on sales_detail_Y2017Q01 (sale_date); create index sales_detail_Y2017Q02_sale_date on sales_detail_Y2017Q02 (sale_date); create index sales_detail_Y2017Q03_sale_date on sales_detail_Y2017Q03 (sale_date); create index sales_detail_Y2017Q04_sale_date on sales_detail_Y2017Q04 (sale_date); --创建触发器，当向sales_detail表中插入数据时，可以重定向插入到分区表中 create or replace function sales_detail_insert_trigger() returns trigger as $$ begin if (new.sale_date >= date ‘2017-01-01‘ and new.sale_date < date ‘2017-04-01‘) then insert into sales_detail_Y2017Q01 values (new.*); elsif (new.sale_date >= date ‘2017-04-01‘ and new.sale_date < date ‘2017-07-01‘) then insert into sales_detail_Y2017Q02 values (new.*); elsif (new.sale_date >= date ‘2017-07-01‘ and new.sale_date < date ‘2017-10-01‘) then insert into sales_detail_Y2017Q03 values (new.*); elsif (new.sale_date >= date ‘2017-10-01‘ and new.sale_date < date ‘2018-01-01‘) then insert into sales_detail_Y2017Q04 values (new.*); else raise exception ‘Date out of range.Fix the sales_detail_insert_trigger () function!‘; end if; return null; end; $$ language plpgsql; create trigger insert_sales_detail_trigger before insert on sales_detail for each row execute procedure sales_detail_insert_trigger (); --设置constraint_exclusion参数为“partition”状态。此参数默认为“partition” set constrait_exclusion ‘partition‘

测试分区表：

--向“父表”中插入一条数据
test=# insert into sales_detail values (1,23.22,1,date‘2017-08-16‘,‘zhaosi‘,‘xiangyashan222hao‘);

--数据已经插入到分区表中
test=# select * from sales_detail_Y2017Q03;
 product_id | price | amount | sale_date  | buyer  |   buyer_contact   
------------+-------+--------+------------+--------+-------------------
          1 | 23.22 |      1 | 2017-08-16 | zhaosi | xiangyashan222hao
(1 row)

--并且查询“父表”也可以查到插入的数据
test=# select * from sales_detail;
 product_id | price | amount | sale_date  | buyer  |   buyer_contact   
------------+-------+--------+------------+--------+-------------------
          1 | 23.22 |      1 | 2017-08-16 | zhaosi | xiangyashan222hao
(1 row)

--通过查看执行计划，可以看出当查询数据时，数据库会自动的去sales_detail_Y2017Q03分区表中查找，而不会扫描所有的分区表。
test=# explain select * from sales_detail where sale_date=date‘2017-08-16‘;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Append  (cost=0.00..9.50 rows=3 width=158)
   ->  Seq Scan on sales_detail  (cost=0.00..0.00 rows=1 width=158)
         Filter: (sale_date = ‘2017-08-16‘::date)
   ->  Bitmap Heap Scan on sales_detail_y2017q03  (cost=4.16..9.50 rows=2 width=158)
         Recheck Cond: (sale_date = ‘2017-08-16‘::date)
         ->  Bitmap Index Scan on sales_detail_y2017q03_sale_date  (cost=0.00..4.16 rows=2 width=0)
               Index Cond: (sale_date = ‘2017-08-16‘::date)
(7 rows)

总结：

删除分区表中的子表，不会使触发器失效，只是当向被删除表中插入数据时会报错。

创建分区表过程中的触发器，可以用“规则”来代替，但触发器比“规则”更有优势，再此不再赘述。

 

The End！

2017-08-17

【PostgreSQL】分区表

标签：exp   replace   var   code   and   报错   bsp   width   str