v61-0001-refactoring-based-on-v61.no-cfbot
application/octet-stream
Filename: v61-0001-refactoring-based-on-v61.no-cfbot
Type: application/octet-stream
Part: 0
From 789e0fa790918ecb845bac634bc25708a3008617 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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 <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
- <varlistentry id="sql-altertable-split-partition">
- <term><literal>SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO (PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }, PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...])</literal></term>
-
- <listitem>
- <para>
- 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 <replaceable class="parameter">partition_name</replaceable>).
- If the split partition is a <literal>DEFAULT</literal> partition, one of
- the new partitions must be <literal>DEFAULT</literal>.
- In case one of the new partitions is <literal>DEFAULT</literal>,
- new partitions <replaceable class="parameter">partition_name1</replaceable>,
- <replaceable class="parameter">partition_name2</replaceable>, ... can
- have spaces between partitions bounds. If the partitioned table does not
- have a <literal>DEFAULT</literal> partition, the <literal>DEFAULT</literal>
- partition can be defined as one of the new partitions.
- </para>
- <para>
- In case new partitions do not contain a <literal>DEFAULT</literal>
- partition, the following must be true: sum bounds of new partitions
- <replaceable class="parameter">partition_name1</replaceable>,
- <replaceable class="parameter">partition_name2</replaceable>, ... should
- be equal to the bound of the split partition <replaceable class="parameter">partition_name</replaceable>.
- One of the new partitions <replaceable class="parameter">partition_name1</replaceable>,
- <replaceable class="parameter">partition_name2</replaceable>, ... can have
- the same name as the split partition <replaceable class="parameter">partition_name</replaceable>
- (this is suitable in case of splitting a <literal>DEFAULT</literal>
- partition: we split it, but after splitting, we have a partition with the
- same name). Only a simple, non-partitioned partition can be split.
- </para>
- <para>
- New partitions will have the same owner as the parent partition.
- It is the user's responsibility to setup <acronym>ACL</acronym> on new
- partitions.
- </para>
- <para>
- 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
- <command>CREATE TABLE PARTITION OF</command>.
- New partitions will inherit the same table access method, persistence
- type, and tablespace as the parent table.
- </para>
- <para>
- When a partition is split, any individual objects belonging to this
- partition, such as constraints or the statistics will be dropped. This occurs
- because <command>ALTER TABLE SPLIT PARTITION</command> uses the partitioned table itself
- as the template to define these objects.
- </para>
- <para>
- If a split partition has some objects dependent on it, the command can
- not be done (<literal>CASCADE</literal> is not used, and an error will
- be returned).
- </para>
- <note>
- <para>
- Split partition acquires an <literal>ACCESS EXCLUSIVE</literal> lock on
- the parent table, in addition to the <literal>ACCESS EXCLUSIVE</literal>
- lock on the table being split.
- </para>
- </note>
- <note>
- <para>
- <command>ALTER TABLE SPLIT PARTITION</command> 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.
- </para>
- </note>
- </listitem>
- </varlistentry>
-
<varlistentry id="sql-altertable-merge-partitions">
<term><literal>MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable></literal></term>
@@ -1334,6 +1261,91 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-altertable-split-partition">
+ <term>
+ <literal>SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO (
+ PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT },
+ PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
+ [, ...])</literal>
+ </term>
+
+ <listitem>
+ <para>
+ 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 <literal>DEFAULT</literal> partition, one of
+ the new partitions must be <literal>DEFAULT</literal>.
+ If the partitioned table does not have <literal>DEFAULT</literal> partition,
+ <literal>DEFAULT</literal> partition can be defined as one of the new partitions.
+ </para>
+
+ <para>
+ Bounds of new partitions should not overlap with new and existing partitions
+ (except <replaceable class="parameter">partition_name</replaceable>).
+ The combined bounds of new partitions <literal>
+ <replaceable class="parameter">partition_name1</replaceable>,
+ <replaceable class="parameter">partition_name2</replaceable>[, ...]
+ </literal> should be equal to the bound of the split partition
+ <replaceable class="parameter">partition_name</replaceable>.
+ One of the new partitions can have the same name as the split partition <replaceable class="parameter">partition_name</replaceable>
+ (this is suitable in case of splitting the <literal>DEFAULT</literal>
+ partition: after the split, the <literal>DEFAULT</literal> partition remains with the same
+ name, but its partition bound changes).
+ </para>
+
+ <para>
+ New partitions will have the same owner as the parent partition.
+ It is the user's responsibility to setup <acronym>ACL</acronym> on new
+ partitions.
+ </para>
+
+ <para>
+ <command>ALTER TABLE SPLIT PARTITION</command> 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ When a partition is split, any objects depend on this partition, such as
+ constraints, triggers, extended statistics etc will be dropped. This occurs
+ because <command>ALTER TABLE SPLIT PARTITION</command> uses the partitioned
+ table itself as the template to reconstruct these objects later.
+ Eventually we will drop the split partition (using <literal>RESTRICT</literal> mode) too,
+ therefore if any objects still dependent on it,
+ <command>ALTER TABLE SPLIT PARTITION</command> would fail.
+ (see <xref linkend="ddl-depend"/>).
+ </para>
+
+ <note>
+ <para>
+ Split partition acquires an <literal>ACCESS EXCLUSIVE</literal> lock on
+ the parent table, in addition to the <literal>ACCESS EXCLUSIVE</literal>
+ lock on the table being split.
+ </para>
+ </note>
+ <note>
+
+ <para>
+ <command>ALTER TABLE SPLIT PARTITION</command> 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.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
@@ -1341,7 +1353,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
All the forms of <command>ALTER TABLE</command> that act on a single table,
except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>,
<literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>,
- <literal>SPLIT PARTITION</literal>, and <literal>MERGE PARTITIONS</literal>
+ <literal>MERGE PARTITIONS</literal>, and <literal>SPLIT PARTITION</literal>
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