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
    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;