为什么不能创建分区表 [英] Why cannot create partitioning table
问题描述
我正在尝试创建带有分区的简单表。
I'm trying to create simple table with partitions.
这是我的命令:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
这是我得到的错误:
SQL错误[42601]:错误:部分处或附近的语法错误
SQL Error [42601]: ERROR: syntax error at or near "PARTITION"
无法理解
我正在使用PostgreSQL 9.6.3
I am using PostgreSQL 9.6.3
推荐答案
"Declarative table partitioning", that is partitioning as a first-class feature of the DBMS with its own syntax, was added in PostgreSQL 10.
在早期版本中,您可以花更多的精力来达到相同的效果使用表继承。手册中有一页,介绍了如何手动执行此操作,概括为:
In earlier versions, you can achieve the same effect with a bit more effort using "table inheritance". There is a page in the manual describing how to do this manually, summarised as:
- 创建 master表,所有分区都将从该表继承。
- 创建几个从主表继承的子表。
- 在分区表中添加表约束,以定义每个分区中允许的键值。
- 对于每个分区,在键列上创建索引,以及可能需要的任何其他索引。
- (可选)定义触发器或规则,以将插入主表中的数据重定向到适当的分区。
- 确保没有将constraint_exclusion配置参数设置为在postgresql.conf中禁用。如果是这样,查询将不会根据需要进行优化。
- Create the "master" table, from which all of the partitions will inherit.
- Create several "child" tables that each inherit from the master table.
- Add table constraints to the partition tables to define the allowed key values in each partition.
- For each partition, create an index on the key column(s), as well as any other indexes you might want.
- Optionally, define a trigger or rule to redirect data inserted into the master table to the appropriate partition.
- Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized as desired.
为了简化操作,如果您无法升级到版本10,您可以使用 pg_partman 这样的扩展名,它为您提供了用于设置和管理分区集的其他功能。
To make this easier, if you can't upgrade to version 10, you can use an extension such as pg_partman which gives you additional functions for setting up and managing partition sets.
这篇关于为什么不能创建分区表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!