直至12版本,PostgreSQL仍没有提供直接拆分分区的功能,暂时只能通过detach&attach实现,相对于Oracle的split支持,PG显得比较的simple&naive. **PG 12** ``` [pg12@localhost ~]$ psql -d testdb Timing is on. Expanded display is used automatically. psql (12beta1) Type "help" for help. [local]:5432 pg12@testdb=# drop table t_p1; ) to (200); create table t_p1_maxvalue partition of t_p1 for values from (200) to (maxvalue); truncate table t_p1; insert into t_p1(id,c1) values(1,1); insert into t_p1(id,c1) values(2,100); insert into t_p1(id,c1) values(3,125); insert into t_p1(id,c1) values(4,200); insert into t_p1(id,c1) values(5,250); insert into t_p1(id,c1) values(6,300); insert into t_p1(id,c1) values(7,350); insert into t_p1(id,c1) values(8,4500); alter table t_p1 detach partition t_p1_maxvalue; create table t_p1_3 partition of t_ERROR: table "t_p1" does not exist Time: 8.497 ms [local]:5432 pg12@testdb=# create table t_p1 (id int, c1 int) partition by range (c1); p1 for values from (200) to (300); insert into t_p1_3 select * from t_p1_maxvalue where c1 >= 200 and c1 < 300; delete from t_p1_maxvalue where c1 >= 200 and c1 < 300; alter table t_p1 attach partition t_p1_maxvalue for values from (300) to (maxvalue);CREATE TABLE Time: 235.099 ms [local]:5432 pg12@testdb=# create table t_p1_default partition of t_p1 default; CREATE TABLE Time: 11.941 ms [local]:5432 pg12@testdb=# create table t_p1_1 partition of t_p1 for values from (1) to (100); CREATE TABLE Time: 15.247 ms [local]:5432 pg12@testdb=# create table t_p1_2 partition of t_p1 for values from (100) to (200); CREATE TABLE Time: 1.705 ms [local]:5432 pg12@testdb=# create table t_p1_maxvalue partition of t_p1 for values from (200) to (maxvalue); CREATE TABLE Time: 1.842 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# truncate table t_p1; TRUNCATE TABLE Time: 3.413 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(1,1); INSERT 0 1 Time: 1.152 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(2,100); INSERT 0 1 Time: 0.871 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(3,125); INSERT 0 1 Time: 0.487 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(4,200); INSERT 0 1 Time: 0.949 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(5,250); INSERT 0 1 Time: 0.494 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(6,300); INSERT 0 1 Time: 0.463 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(7,350); INSERT 0 1 Time: 0.481 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(8,4500); INSERT 0 1 Time: 0.464 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# alter table t_p1 detach partition t_p1_maxvalue; ALTER TABLE Time: 0.864 ms [local]:5432 pg12@testdb=# create table t_p1_3 partition of t_p1 for values from (200) to (300); CREATE TABLE Time: 1.752 ms [local]:5432 pg12@testdb=# insert into t_p1_3 select * from t_p1_maxvalue where c1 >= 200 and c1 < 300; INSERT 0 2 Time: 7.578 ms [local]:5432 pg12@testdb=# delete from t_p1_maxvalue where c1 >= 200 and c1 < 300; DELETE 2 Time: 21.992 ms [local]:5432 pg12@testdb=# alter table t_p1 attach partition t_p1_maxvalue for values from (300) to (maxvalue); ALTER TABLE Time: 7.356 ms [local]:5432 pg12@testdb=# ``` **Oracle** ``` TEST-orcl@DESKTOP-V430TU3>create table t_p1(id int,c1 int) 2 partition by range(c1) 3 (partition p1 values less than(100), 4 partition p2 values less than(200), 5 partition pmax values less than(maxvalue) 6 ); Table created. TEST-orcl@DESKTOP-V430TU3> TEST-orcl@DESKTOP-V430TU3>truncate table t_p1; Table truncated. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(1,1); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(2,100); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(3,125); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(4,200); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(5,250); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(6,300); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(7,350); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(8,4500); 1 row created. TEST-orcl@DESKTOP-V430TU3>alter table t_p1 split partition pmax at(1000) into (partition p3,partition pmx); Table altered. TEST-orcl@DESKTOP-V430TU3> ``` 可以参照EDB的做法,加入此兼容性. **参考资料** [13.3 Partitioning Commands Compatible with Oracle Databases](https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/9.5/Database_Compatibility_for_Oracle_Developers_Guide.1.329.html#)