PostgreSQL删除分区表 [英] postgresql delete partition tables

问题描述

我是Postgresql的新手，并不完全知道如何管理分区表.
我有基于天的分区表.在插入数据之前，触发器会检查日期并将其放入相应的子表中.
例如
2014年11月11日-插入名为11-15-2014_log的表中.
2014年11月16日-插入名为11-16-2014_log的表中.
现在，我想创建将删除旧子表(例如，超过90天的表)的函数.我应该根据子表的表名查找和删除子表(因为它包含创建日期)，还是应该在主表中找到超过90天的记录?
任何建议或提示将不胜感激.谢谢.

I'm new at Postgresql and do not exactly know how to manage partition tables.
I have partition table based on day. Before inserting data, trigger checks date and put into corresponding child table.
eg.
11.15.2014 - insert into table named 11-15-2014_log.
11.16.2014 - insert into table named 11-16-2014_log.
Now I want to create function that will drop old child tables, for example, tables older than 90 days. Should I find and drop child table according its tablename (cause it consists date of creation) OR should I find records older than 90 days in master table??
Any suggestion or hint will be appreciated. Thanks.

推荐答案

，下面的示例删除sales表的分区.使用以下命令创建销售表:

regarding dropping a partition, The example that follows deletes a partition of the sales table. Use the following command to create the sales table:

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date    date,
  amount  number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

查询 ALL_TAB_PARTITIONS 视图显示分区名称:

Querying the ALL_TAB_PARTITIONS view displays the partition names:

acctg=# SELECT partition_name, server_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name | server_name |     high_value      
----------------+-------------+---------------------
 europe         | seattle     | 'FRANCE', 'ITALY'
 asia           | chicago     | 'INDIA', 'PAKISTAN'
 americas       | boston      | 'US', 'CANADA'
(3 rows)

要从sales表中删除Americas分区，请调用以下命令:

To delete the americas partition from the sales table, invoke the following command:

ALTER TABLE sales DROP PARTITION americas;

查询 ALL_TAB_PARTITIONS 视图表明该分区已成功删除:

Querying the ALL_TAB_PARTITIONS view demonstrates that the partition has been successfully deleted:

acctg=# SELECT partition_name, server_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 asia           | 'INDIA', 'PAKISTAN'
 europe         | 'FRANCE', 'ITALY'
(2 rows)

这篇关于PostgreSQL删除分区表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！