postgres分区表修改分区规则

需求描述：如按列表分区，有一个分区表里存放的是 l_type in ('1'),现在想把这个分区改为t_type in ('1', '3')。
postgres好像没有直接提供这样的方法，需要间接地进行一下修改，这里会用到 ATTACH PARTITION 和 DETACH PARTITION。

为了演示，准备工作就是把数据建出来
创建父表

create table list_partition_test(id serial, l_type char(1)) partition by list( l_type );

创建子表

create table list_partition_test_1 partition of list_partition_test for values in ('1');
create table list_partition_test_2 partition of list_partition_test for values in ('2');

随意地添加数据，注意这里没有添加默认分区，也就是说不能太随意，如果添加l_type=3的记录是添加不进去的。

insert into list_partition_test (l_type) values ('1');
insert into list_partition_test (l_type) values ('2');
insert into list_partition_test (l_type) values ('2');
insert into list_partition_test (l_type) values ('2');
insert into list_partition_test (l_type) values ('1');

查一下数据吧

# 全表
select * from list_partition_test;
 id | l_type
----+--------
  1 | 1
  5 | 1
  2 | 2
  3 | 2
  4 | 2
(5 rows)
# 分区1
select * from list_partition_test_1;
 id | l_type
----+--------
  1 | 1
  5 | 1
(2 rows)
# 分区2
select * from list_partition_test_2;
 id | l_type
----+--------
  2 | 2
  3 | 2
  4 | 2
(3 rows)

现在想让分区1也能插入l_type=3的记录，需要这么做一下。
先看一下添加l_type=3会怎么样吧

insert into list_partition_test (l_type) values ('3');
ERROR:  no partition of relation "list_partition_test" found for row
DETAIL:  Partition key of the failing row contains (l_type) = (3).

人家没让添加成功
下面让它能添加成功
先将分区1从父表移除 – DETACH

ALTER TABLE list_partition_test DETACH PARTITION list_partition_test_1;

# 查一下数据
select * from list_partition_test;
 id | l_type
----+--------
  2 | 2
  3 | 2
  4 | 2
(3 rows)

再查数据时发现分区1的数据没了，当然会这样，因为数据就在人家分区1里，又不在list_partition_test里。不过不要慌，它又没丢。
这个时候把分区1再添加进来

ALTER TABLE list_partition_test ATTACH PARTITION list_partition_test_1  FOR VALUES IN ('1', '3');

# 再查一下数据
select * from list_partition_test;
 id | l_type
----+--------
  1 | 1
  5 | 1
  2 | 2
  3 | 2
  4 | 2
(5 rows)

再查数据时，发现数据已经回来了。就说不要慌的。
但是这里注意，添加分区1时，要保证list_partition_test_1表里的数据l_type都是1或3，要是再有个l_type=4，就ATTACH不成功了。
下面试一下效果，添加一条l_type=3的数据。

insert into list_partition_test (l_type) values ('3');

# 查一下数据再，可以发现l_type=3的数据已经插入成功了
select * from list_partition_test;
 id | l_type
----+--------
  1 | 1
  5 | 1
  7 | 3
  2 | 2
  3 | 2
  4 | 2
(6 rows)

# 而且l_type=3的数据在分区1里
select * from list_partition_test_1;
 id | l_type
----+--------
  1 | 1
  5 | 1
  7 | 3

OKAY，搞完了。

生产环境数据量通常会很大，也可以采用这样的方法，但要小心慢来。