Postgresql10分区表range实例

1. PG10不能在父表上创建索引
2. 极限优化索引可以创建条件索引
3. 序列一定是要挂在父表上

PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ opclass ] [, ...] )
The optional PARTITION BY clause specifies a strategy of partitioning the table. The table thus created is called a partitioned table. The parenthesized list of columns or expressions forms the partition key for the table. When using range partitioning, the partition key can include multiple columns or expressions (up to 32, but this limit can be altered when building PostgreSQL), but for list partitioning, the partition key must consist of a single column or expression. If no B-tree operator class is specified when creating a partitioned table, the default B-tree operator class for the datatype will be used. If there is none, an error will be reported.

A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. If no existing partition matches the values in the new row, an error will be reported.

Partitioned tables do not support UNIQUE, PRIMARY KEY, EXCLUDE, or FOREIGN KEY constraints; however, you can define these constraints on individual partitions.

create table t__root(id int, info text) partition by range(info);
create table t_0 partition of t__root for values from ('0') to ('1');
create table t_1 partition of t__root for values from ('1') to ('2');
create table t_2 partition of t__root for values from ('2') to ('3');
create table t_3 partition of t__root for values from ('3') to ('4');

alter table t_0 add constraint idx_t_0_id_pkey primary key(id);
alter table t_1 add constraint idx_t_1_id_pkey primary key(id);
alter table t_2 add constraint idx_t_2_id_pkey primary key(id);
alter table t_3 add constraint idx_t_3_id_pkey primary key(id);

alter table t_0 add constraint idx_t_0_id_unique unique(id);
alter table t_1 add constraint idx_t_1_id_unique unique(id);
alter table t_2 add constraint idx_t_2_id_unique unique(id);
alter table t_3 add constraint idx_t_3_id_unique unique(id);


CREATE SEQUENCE t__root_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
alter table t__root alter column id set default nextval('t__root_id_seq');
select setval('t__root_id_seq', 10000, true);