postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
postgres=#
在ppstgresql 10中，如果在分区表上创建索引，会发生什么呢？
postgres=# create index idx_test on part (a); ERROR: cannot create index on partitioned table "part" postgres=#
无法创建索引。但是可以在分区上创建索引：
postgres=# create index idx_test_1 on part_1 (a); CREATE INDEX postgres=# create index idx_test_2 on part_2 (a); CREATE INDEX postgres=#
再来看看postgresql 11中是什么样子的。
postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
postgres=#
再次尝试在分区表上创建索引：
postgres=# create index idx_test on part (a);
CREATE INDEX
postgres=# d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition key: LIST (list)
Indexes:
"idx_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
postgres=# d+ part_1
Table "public.part_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
"part_1_a_idx" btree (a)
postgres=# d+ part_2
Table "public.part_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
"part_2_a_idx" btree (a)
postgres=#
在postgresql 11中，分区表上创建的索引被级联到了分区上。
此外，在postgresql10中，如果想对分区表添加主键约束，会失败：
postgres=# alter table part add constraint part_pk primary key(a,list);
ERROR: primary key constraints are not supported on partitioned tables
LINE 1: alter table part add constraint part_pk primary key(a,list);
^
postgres=#
不过，却可以在分区上添加主键约束：
postgres=# alter table part_1 add constraint part1_pk primary key(a,list); ALTER TABLE postgres=# alter table part_2 add constraint part2_pk primary key(a,list); ALTER TABLE postgres=#
在postgresql11中，可以直接在分区表上添加主键约束，添加成功后，会自动添加到分区上：
postgres=# alter table part add constraint part_pk primary key(a,list);
ALTER TABLE
postgres=# d+ part_1
Table "public.part_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
list | character varying(5) | | not null | | extended | |
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
"part_1_pkey" PRIMARY KEY, btree (a, list)
"part_1_a_idx" btree (a)
postgres=# d+ part_2
Table "public.part_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
list | character varying(5) | | not null | | extended | |
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
"part_2_pkey" PRIMARY KEY, btree (a, list)
"part_2_a_idx" btree (a)
postgres=# d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
list | character varying(5) | | not null | | extended | |
Partition key: LIST (list)
Indexes:
"part_pk" PRIMARY KEY, btree (a, list)
"idx_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
postgres=#
不过，在分区表上添加的主键约束，必须包含分区列：
postgres=# alter table part add constraint part_pk primary key(a); ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "part" lacks column "list" which is part of the partition key. postgres=#