PostgreSQL 10 版本已支持分区表，但不支持分区表根据分区键UPDATE记录，PostgreSQL 11 版本这方面得到增加，当分区表的分区键字段被UPDATE后，会自动将该记录转移至新的分区中。
官网Release说明如下：
UPDATE statements that change a partition key now move affected rows to the appropriate partitions
本文以UPDATE列表分区表分区键为例进行演示。
测试环境准备
创建列表分区表并插入测试数据，为演示做准备。
创建父表
CREATE TABLE cities (
city_id bigserial not null,
name text not null,
population bigint
) PARTITION BY LIST (left(lower(name), 1));创建子表
CREATE TABLE cities_a PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('a');
CREATE TABLE cities_b PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('b');
CREATE TABLE cities_c PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('c');插入测试数据
INSERT INTO cities(city_id,name,population) VALUES (1,'a_city','100000');
INSERT INTO cities(city_id,name,population) VALUES (2,'b_city','200000');
INSERT INTO cities(city_id,name,population) VALUES (3,'c_city','300000');PostgreSQL 10 测试
PostgreSQL 10 版本UPDATE分区键报错，如下：
mydb=> SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)
mydb=> UPDATE cities SET name='ca_city' WHERE city_id=1;
ERROR: new row for relation "cities_a" violates partition constraint
DETAIL: Failing row contains (1, ca_city, 100000).PostgreSQL 11 测试
PostgreSQL 11 版本支持更新分区键，如下:
francs=> SELECT version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 11beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)
francs=> UPDATE cities SET name='ca_city' WHERE city_id=1;
UPDATE 1
francs=> SELECT * FROM cities;
city_id | name | population
---------+---------+------------
2 | b_city | 200000
3 | c_city | 300000
1 | ca_city | 100000
(3 rows)查看cities_c分区，发现city_id为1的记录已转移到此分区，如下：
francs=> SELECT * from cities_c;
city_id | name | population
---------+---------+------------
3 | c_city | 300000
1 | ca_city | 100000
(2 rows)参考
- PostgreSQL10: 重量级新特性-支持分区表
- PostgreSQL11: 分区表增加哈希分区
- PostgreSQL11: 分区表支持创建主键、外键、索引
- PostgreSQL11: 分区表支持UPDATE分区键
- PostgreSQL11: 分区表增加 Default Partition
- PostgreSQL11: Partitioning Improvements
新书推荐
最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例!
