From 789e0fa790918ecb845bac634bc25708a3008617 Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 1 Oct 2025 12:59:09 +0800 Subject: [PATCH v61 1/1] refactoring based on v61 --- doc/src/sgml/ref/alter_table.sgml | 160 ++++++++++-------- src/include/nodes/parsenodes.h | 20 ++- src/test/regress/expected/partition_split.out | 82 ++++----- src/test/regress/sql/partition_split.sql | 54 +++--- 4 files changed, 163 insertions(+), 153 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index d8e8383b071..05fbc27309a 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1165,79 +1165,6 @@ WITH ( MODULUS numeric_literal, REM - - SPLIT PARTITION partition_name INTO (PARTITION partition_name1 { FOR VALUES partition_bound_spec | DEFAULT }, PARTITION partition_name2 { FOR VALUES partition_bound_spec | DEFAULT } [, ...]) - - - - This form splits a single partition of the target table into new - partitions. Hash-partitioned target table is not supported. Bounds of new - partitions should not overlap with new and existing partitions - (except partition_name). - If the split partition is a DEFAULT partition, one of - the new partitions must be DEFAULT. - In case one of the new partitions is DEFAULT, - new partitions partition_name1, - partition_name2, ... can - have spaces between partitions bounds. If the partitioned table does not - have a DEFAULT partition, the DEFAULT - partition can be defined as one of the new partitions. - - - In case new partitions do not contain a DEFAULT - partition, the following must be true: sum bounds of new partitions - partition_name1, - partition_name2, ... should - be equal to the bound of the split partition partition_name. - One of the new partitions partition_name1, - partition_name2, ... can have - the same name as the split partition partition_name - (this is suitable in case of splitting a DEFAULT - partition: we split it, but after splitting, we have a partition with the - same name). Only a simple, non-partitioned partition can be split. - - - New partitions will have the same owner as the parent partition. - It is the user's responsibility to setup ACL on new - partitions. - - - The indexes and identity are created later, after moving the data - into the new partitions. - Extended statistics aren't copied from the parent table, for consistency with - CREATE TABLE PARTITION OF. - New partitions will inherit the same table access method, persistence - type, and tablespace as the parent table. - - - When a partition is split, any individual objects belonging to this - partition, such as constraints or the statistics will be dropped. This occurs - because ALTER TABLE SPLIT PARTITION uses the partitioned table itself - as the template to define these objects. - - - If a split partition has some objects dependent on it, the command can - not be done (CASCADE is not used, and an error will - be returned). - - - - Split partition acquires an ACCESS EXCLUSIVE lock on - the parent table, in addition to the ACCESS EXCLUSIVE - lock on the table being split. - - - - - ALTER TABLE SPLIT PARTITION creates new partitions and - moves data from the split partition into them, which can take a long - time. So it is not recommended to use the command for splitting a - small fraction of rows out of a very big partition. - - - - - MERGE PARTITIONS (partition_name1, partition_name2 [, ...]) INTO partition_name @@ -1334,6 +1261,91 @@ WITH ( MODULUS numeric_literal, REM + + + SPLIT PARTITION partition_name INTO ( + PARTITION partition_name1 { FOR VALUES partition_bound_spec | DEFAULT }, + PARTITION partition_name2 { FOR VALUES partition_bound_spec | DEFAULT } + [, ...]) + + + + + This form splits a single partition of the target table into new + partitions. Hash-partitioned target table is not supported. + Only a simple, non-partitioned partition can be split. + If the split partition is the DEFAULT partition, one of + the new partitions must be DEFAULT. + If the partitioned table does not have DEFAULT partition, + DEFAULT partition can be defined as one of the new partitions. + + + + Bounds of new partitions should not overlap with new and existing partitions + (except partition_name). + The combined bounds of new partitions + partition_name1, + partition_name2[, ...] + should be equal to the bound of the split partition + partition_name. + One of the new partitions can have the same name as the split partition partition_name + (this is suitable in case of splitting the DEFAULT + partition: after the split, the DEFAULT partition remains with the same + name, but its partition bound changes). + + + + New partitions will have the same owner as the parent partition. + It is the user's responsibility to setup ACL on new + partitions. + + + + ALTER TABLE SPLIT PARTITION uses the partitioned table itself + as the template to construct new partitions. + New partitions will inherit the same table access method, persistence + type, and tablespace as the partitioned table. + + + + Constraints, column defaults, column generation expressions, identity columns, + indexes, triggers are copied from the partitioned table to the new + partitions. But extended statistics, security policies etc won't copied from + the partitioned table. + Indexes and identity columns copied from the partitioned table will be + created afterward, once the data has been moved into the new partitions. + + + + When a partition is split, any objects depend on this partition, such as + constraints, triggers, extended statistics etc will be dropped. This occurs + because ALTER TABLE SPLIT PARTITION uses the partitioned + table itself as the template to reconstruct these objects later. + Eventually we will drop the split partition (using RESTRICT mode) too, + therefore if any objects still dependent on it, + ALTER TABLE SPLIT PARTITION would fail. + (see ). + + + + + Split partition acquires an ACCESS EXCLUSIVE lock on + the parent table, in addition to the ACCESS EXCLUSIVE + lock on the table being split. + + + + + + ALTER TABLE SPLIT PARTITION creates new partitions and + moves data from the split partition into them, which can take a long + time. So it is not recommended to use the command for splitting a + small fraction of rows out of a very big partition. + + + + + @@ -1341,7 +1353,7 @@ WITH ( MODULUS numeric_literal, REM All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION, DETACH PARTITION, - SPLIT PARTITION, and MERGE PARTITIONS + MERGE PARTITIONS, and SPLIT PARTITION can be combined into a list of multiple alterations to be applied together. For example, it is possible to add several columns and/or alter the type of several diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 28414109242..03afa65ed81 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -982,11 +982,21 @@ typedef struct SinglePartitionSpec typedef struct PartitionCmd { NodeTag type; - RangeVar *name; /* name of partition to - * attach/detach/merge/split */ - PartitionBoundSpec *bound; /* FOR VALUES, if attaching */ - List *partlist; /* list of partitions to be split/merged, used - * in ALTER TABLE SPLIT/MERGE PARTITION(S) */ + + /* name of partition to attach/detach/merge/split */ + RangeVar *name; + + /* FOR VALUES, if attaching */ + PartitionBoundSpec *bound; + + /* + * list of partitions to be split/merged, used in + * ALTER TABLE MERGE PARTITOINS and ALTER TABLE SPLIT PARTITOINS. + * For merge partitions, partlist is a list of RangeVar; For split + * partition, it is a list of SinglePartitionSpec. + */ + List *partlist; + bool concurrent; } PartitionCmd; diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out index 250123bd928..e68baf71daf 100644 --- a/src/test/regress/expected/partition_split.out +++ b/src/test/regress/expected/partition_split.out @@ -440,108 +440,96 @@ SELECT * FROM sales_others where sales_date > '2022-01-01'; (1 row) RESET enable_seqscan; -SELECT * FROM pg_indexes WHERE tablename = 'sales_feb2022' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; +SELECT * FROM pg_indexes +WHERE tablename in ('sales_feb2022', 'sales_mar2022', 'sales_apr2022', 'sales_others') +AND schemaname = 'partition_split_schema' +ORDER BY indexname COLLATE "C"; schemaname | tablename | indexname | tablespace | indexdef ------------------------+---------------+------------------------------+------------+------------------------------------------------------------------------------------------------------------ + partition_split_schema | sales_apr2022 | sales_apr2022_sales_date_idx | | CREATE INDEX sales_apr2022_sales_date_idx ON partition_split_schema.sales_apr2022 USING btree (sales_date) partition_split_schema | sales_feb2022 | sales_feb2022_sales_date_idx | | CREATE INDEX sales_feb2022_sales_date_idx ON partition_split_schema.sales_feb2022 USING btree (sales_date) -(1 row) - -SELECT * FROM pg_indexes WHERE tablename = 'sales_mar2022' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; - schemaname | tablename | indexname | tablespace | indexdef -------------------------+---------------+------------------------------+------------+------------------------------------------------------------------------------------------------------------ partition_split_schema | sales_mar2022 | sales_mar2022_sales_date_idx | | CREATE INDEX sales_mar2022_sales_date_idx ON partition_split_schema.sales_mar2022 USING btree (sales_date) -(1 row) - -SELECT * FROM pg_indexes WHERE tablename = 'sales_apr2022' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; - schemaname | tablename | indexname | tablespace | indexdef -------------------------+---------------+------------------------------+------------+------------------------------------------------------------------------------------------------------------ - partition_split_schema | sales_apr2022 | sales_apr2022_sales_date_idx | | CREATE INDEX sales_apr2022_sales_date_idx ON partition_split_schema.sales_apr2022 USING btree (sales_date) -(1 row) - -SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; - schemaname | tablename | indexname | tablespace | indexdef -------------------------+--------------+------------------------------+------------+----------------------------------------------------------------------------------------------------------- - partition_split_schema | sales_others | sales_others_sales_date_idx1 | | CREATE INDEX sales_others_sales_date_idx1 ON partition_split_schema.sales_others USING btree (sales_date) -(1 row) + partition_split_schema | sales_others | sales_others_sales_date_idx1 | | CREATE INDEX sales_others_sales_date_idx1 ON partition_split_schema.sales_others USING btree (sales_date) +(4 rows) DROP TABLE sales_range CASCADE; -- -- Test: some cases for splitting DEFAULT partition (different bounds) -- -CREATE TABLE sales_range (salesperson_id INT, sales_amount INT) PARTITION BY RANGE (sales_amount); +CREATE TABLE sales_range (salesperson_id INT, sales_date date) PARTITION BY RANGE (sales_date); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- sales_error intersects with sales_dec2021 (lower bound) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20211230) TO (20220201), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2021-12-30') TO ('2022-02-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" -LINE 3: PARTITION sales_error FOR VALUES FROM (20211230) TO (2022... +LINE 3: PARTITION sales_error FOR VALUES FROM ('2021-12-30') TO (... ^ DETAIL: lower bound of partition "sales_error" is not equal to the upper bound of partition "sales_dec2021" HINT: ALTER TABLE ... SPLIT PARTITION requires the partition bounds to be adjacent. -- sales_error intersects with sales_feb2022 (upper bound) -- ERROR: can not split to partition "sales_feb2022" together with partition "sales_error" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20220101) TO (20220202), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2022-01-01') TO ('2022-02-02'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); ERROR: can not split to partition "sales_feb2022" together with partition "sales_error" -LINE 4: PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20... +LINE 4: PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO... ^ DETAIL: lower bound of partition "sales_feb2022" is not equal to the upper bound of partition "sales_error" HINT: ALTER TABLE ... SPLIT PARTITION requires the partition bounds to be adjacent. -- sales_error intersects with sales_dec2021 (inside bound) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20211210) TO (20211220), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2021-12-10') TO ('2021-12-20'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" -LINE 3: PARTITION sales_error FOR VALUES FROM (20211210) TO (2021... +LINE 3: PARTITION sales_error FOR VALUES FROM ('2021-12-10') TO (... ^ DETAIL: lower bound of partition "sales_error" is not equal to the upper bound of partition "sales_dec2021" HINT: ALTER TABLE ... SPLIT PARTITION requires the partition bounds to be adjacent. -- sales_error intersects with sales_dec2021 (exactly the same bounds) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" -LINE 3: PARTITION sales_error FOR VALUES FROM (20211201) TO (2022... +LINE 3: PARTITION sales_error FOR VALUES FROM ('2021-12-01') TO (... ^ DETAIL: lower bound of partition "sales_error" is not equal to the upper bound of partition "sales_dec2021" HINT: ALTER TABLE ... SPLIT PARTITION requires the partition bounds to be adjacent. -- ERROR: can not split DEFAULT partition "sales_others" -- HINT: To split DEFAULT partition one of the new partition msut be DEFAULT ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_jan2022 FOR VALUES FROM (20220101) TO (20220201), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301)); + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_jan2022 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01')); ERROR: can not split DEFAULT partition "sales_others" -LINE 2: (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20... +LINE 2: (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO... ^ HINT: To split DEFAULT partition one of the new partition msut be DEFAULT -- no error: bounds of sales_noerror are between sales_dec2021 and sales_feb2022 ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_noerror FOR VALUES FROM (20220110) TO (20220120), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_noerror FOR VALUES FROM ('2022-01-10') TO ('2022-01-20'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); DROP TABLE sales_range; -CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date); +CREATE TABLE sales_range (sales_date date) PARTITION BY RANGE (sales_date); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2021 and sales_feb2022 ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_noerror FOR VALUES FROM (20220101) TO (20220201), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_noerror FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); DROP TABLE sales_range; -- diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql index ffd95a43065..0e79c036ea9 100644 --- a/src/test/regress/sql/partition_split.sql +++ b/src/test/regress/sql/partition_split.sql @@ -285,75 +285,75 @@ SELECT * FROM sales_others where sales_date > '2022-01-01'; RESET enable_seqscan; -SELECT * FROM pg_indexes WHERE tablename = 'sales_feb2022' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; -SELECT * FROM pg_indexes WHERE tablename = 'sales_mar2022' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; -SELECT * FROM pg_indexes WHERE tablename = 'sales_apr2022' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; -SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema' ORDER BY indexname COLLATE "C"; +SELECT * FROM pg_indexes +WHERE tablename in ('sales_feb2022', 'sales_mar2022', 'sales_apr2022', 'sales_others') +AND schemaname = 'partition_split_schema' +ORDER BY indexname COLLATE "C"; DROP TABLE sales_range CASCADE; -- -- Test: some cases for splitting DEFAULT partition (different bounds) -- -CREATE TABLE sales_range (salesperson_id INT, sales_amount INT) PARTITION BY RANGE (sales_amount); +CREATE TABLE sales_range (salesperson_id INT, sales_date date) PARTITION BY RANGE (sales_date); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- sales_error intersects with sales_dec2021 (lower bound) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20211230) TO (20220201), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2021-12-30') TO ('2022-02-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); -- sales_error intersects with sales_feb2022 (upper bound) -- ERROR: can not split to partition "sales_feb2022" together with partition "sales_error" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20220101) TO (20220202), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2022-01-01') TO ('2022-02-02'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); -- sales_error intersects with sales_dec2021 (inside bound) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20211210) TO (20211220), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2021-12-10') TO ('2021-12-20'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); -- sales_error intersects with sales_dec2021 (exactly the same bounds) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_error FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_error FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); -- ERROR: can not split DEFAULT partition "sales_others" -- HINT: To split DEFAULT partition one of the new partition msut be DEFAULT ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_jan2022 FOR VALUES FROM (20220101) TO (20220201), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301)); + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_jan2022 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01')); -- no error: bounds of sales_noerror are between sales_dec2021 and sales_feb2022 ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_noerror FOR VALUES FROM (20220110) TO (20220120), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_noerror FOR VALUES FROM ('2022-01-10') TO ('2022-01-20'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); DROP TABLE sales_range; -CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date); +CREATE TABLE sales_range (sales_date date) PARTITION BY RANGE (sales_date); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2021 and sales_feb2022 ALTER TABLE sales_range SPLIT PARTITION sales_others INTO - (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_noerror FOR VALUES FROM (20220101) TO (20220201), - PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), + (PARTITION sales_dec2021 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01'), + PARTITION sales_noerror FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'), + PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), PARTITION sales_others DEFAULT); DROP TABLE sales_range; -- 2.34.1