ALTER PARTITION TABLE - Hologres - Alibaba Cloud Documentation Center

This topic describes how to execute the ALTER PARTITION TABLE statement to modify a partitioned table.

Syntax

You can execute one of the following statements to modify a partitioned table in Hologres:

ALTER TABLE [IF EXISTS] table_name RENAME to new_table_name;
ALTER TABLE [IF EXISTS] table_name ATTACH PARTITION new_partition_name FOR VALUES in (<string_literal>);
ALTER TABLE [IF EXISTS] table_name DETACH PARTITION paritition_name;

Parameters

The following table describes the parameters in the ALTER PARTITION TABLE statement that is used to modify a partitioned table.

Parameter	Description
`RENAME`	The clause that renames the partitioned table.
`ATTACH PARTITION new_partition_name FOR VALUES in (<string_literal>)`	The clause that attaches a table to a partitioned table as a partition. Take note of the following rules: You must attach the table based on the partition policy and partition key of the partitioned table. The table to be attached must have the same number of fields as the partitioned table. The data types of the fields must match. The table to be attached must have all the NOT NULL constraints of the partitioned table. If you attach a list partition that does not accept NULL values, add the NOT NULL constraint to the partition key, unless the primary key is an expression.
`DETACH PARTITION partition_name`	The clause that detaches a specified partition from a partitioned table. The detached partition still exists as a standalone table, but no longer has ties to the table from which it is detached.

Limits

The following list and table describe the rules on property settings that apply when a child table is attached to a parent table.

Must be consistent with the parent table: A property of the child table must be consistent with that of the parent table. Otherwise, an error is reported when the child table is attached to the parent table, and another child table must be created.
Need not be consistent with the parent table: A property of the child table can be different from that of the parent table. If the property of the child table is not explicitly specified, the child table inherits the corresponding property setting of the parent table. If the property of the child table is explicitly specified, the property setting of the child table is retained.
Must include indexed columns of the parent table: The indexed columns of the child table must include those of the parent table. Columns that are not specified as indexed columns for the parent table can be explicitly specified for the child table.

Category	Table property	Description	Whether a child table created by executing the CREATE TABLE PARTITION OF statement inherits the property setting from its parent table	Rule that applies when a child table is attached to a parent table	Whether a child table that is detached from a parent table inherits the property setting from the parent table
Table property	orientation	The storage format of the table.	Yes	Must be consistent with the parent table	Yes
	table_group	The table group to which the table belongs. This property also specifies the shard count for the table group.	Yes	Must be consistent with the parent table	Yes
	time_to_live_in_seconds	The time-to-live (TTL) period of the table.	Yes	Need not be consistent with the parent table If this property is not specified for the child table, the child table inherits the property setting from its parent table. If this property is specified for the child table, the specified property setting is retained.	Yes
Index	primary key	The primary key of the table.	Yes	Must be consistent with the parent table	Yes
	distribution key	The distribution key of the table.	Yes	Must be consistent with the parent table	Yes
	clustering_key	The clustered index of the table. This property specifies the columns and the order in which data is stored in the table.	Yes	Must be consistent with the parent table	Yes
	event_time_column	The segment key of the table.	Yes	Must be consistent with the parent table	Yes
	bitmap_columns	The bitmap indexes of the table.	Yes	Must include indexed columns of the parent table	Yes
	dictionary_encoding_columns	The field indexes of the table.	Yes	Must include indexed columns of the parent table	Yes
	binlog_level	Specifies whether to enable binary logging.	Yes	Must be consistent with the parent table	Yes
	proxima_vectors	The indexes used to perform vector searches on the table.	Yes	Must include indexed columns of the parent table	Yes
Column constraint	nullable	The NOT NULL constraint.	Yes	Must be consistent with the parent table	Yes
Column constraint	default value	The default value constraint.	Yes	Must be consistent with the parent table	Yes

The table to be attached must have the same number of fields as the partitioned table.
The data types of the fields must match.

Examples

The following sample code provides examples on how to execute the ALTER PARTITION TABLE statement to modify a partitioned table:

-- Rename a partitioned table.
alter table holo_test rename to my_holo_test;

-- Attach the table named my_table as a partition of the table named holo_table.
alter table holo_table attach partition my_table for values in ('2015');

-- Detach the child partitioned table holo_test from the parent partitioned table all_test and make the child partitioned table a standalone table.
alter table all_test detach partition holo_test;

The following sample code provides an example on how to replace an existing child partitioned table:

-- Create a temporary table.
begin;
drop table if exists "table_20210101_new";
CREATE TABLE "table_20210101_new" (
  "colA" integer NOT NULL,
  "colB" text NOT NULL,
  "colC" numeric(38,10) NOT NULL,
  "ds" text NOT NULL,
  "process_time" timestamptz NOT NULL DEFAULT now()
);
call set_table_property('table_20210101_new', 'orientation','column');
call set_table_property('table_20210101_new', 'distribution_key','"colA"');
call set_table_property('table_20210101_new', 'event_time_column','process_time');
commit;

--- Import data to the temporary table.
insert into "table_20210101_new" select * from ...;

--- Replace an existing child partitioned table with the temporary table.
begin;
-- Detach an existing child partitioned table from the parent partitioned table and make the child partitioned table a standalone table.
ALTER TABLE table_parent DETACH PARTITION table_20210101;
-- Rename the standalone table.
ALTER TABLE table_20210101 RENAME to table_20210101_backup;
-- Rename the temporary table as the original child partitioned table.
ALTER TABLE table_20210101_new RENAME to table_20210101;
-- Attach the new child partitioned table to the parent partitioned table.
ALTER TABLE table_parent ATTACH PARTITION table_20210101 FOR VALUES in ("20210101");
commit;