ALTER TABLE

Change the definition of a table.

Overview

ALTER TABLE [IF EXISTS] [ONLY] name 
    action [, ... ]

ALTER TABLE [IF EXISTS] [ONLY] name 
    RENAME [COLUMN] column_name TO new_column_name

ALTER TABLE [ IF EXISTS ] [ ONLY ] name 
    RENAME CONSTRAINT constraint_name TO new_constraint_name

ALTER TABLE [IF EXISTS] name 
    RENAME TO new_name

ALTER TABLE [IF EXISTS] name 
    SET SCHEMA new_schema

ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

ALTER TABLE [IF EXISTS] name 
    SET (encodechain=new_encodechain)

ALTER TABLE [IF EXISTS] [ONLY] name SET 
     WITH (REORGANIZE=true|false)
   | DISTRIBUTED BY ({column_name [opclass]} [, ... ] )
   | DISTRIBUTED RANDOMLY
   | DISTRIBUTED REPLICATED

ALTER TABLE [IF EXISTS] [ONLY] name SET SEGMENT_SET segment_set_name

ALTER TABLE name
   [ ALTER PARTITION { partition_name | FOR (RANK(number)) 
   | FOR (value) } [...] ]  partition_action 
   partition_action

Where action is one of the following:

  ADD [COLUMN] column_name data_type [ DEFAULT default_expr ]
      [column_constraint [ ... ]]
      [ COLLATE collation ]
      [ ENCODING ( storage_directive [,...] ) ]
  DROP [COLUMN] [IF EXISTS] column_name [RESTRICT | CASCADE]
  ALTER [COLUMN] column_name [ SET DATA ] TYPE type [COLLATE collation] [USING expression]
  ALTER [COLUMN] column_name SET DEFAULT expression
  ALTER [COLUMN] column_name DROP DEFAULT
  ALTER [COLUMN] column_name { SET | DROP } NOT NULL
  ALTER [COLUMN] column_name SET STATISTICS integer
  ALTER [COLUMN] column SET ( attribute_option = value [, ... ] )
  ALTER [COLUMN] column RESET ( attribute_option [, ... ] )
  ADD table_constraint [NOT VALID]
  ADD table_constraint_using_index
  VALIDATE CONSTRAINT constraint_name
  DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT | CASCADE]
  DISABLE TRIGGER [trigger_name | ALL | USER]
  ENABLE TRIGGER [trigger_name | ALL | USER]
  CLUSTER ON index_name
  SET WITHOUT CLUSTER
  SET WITHOUT OIDS
  SET (storage_parameter = value)
  RESET (storage_parameter [, ... ])
  INHERIT parent_table
  NO INHERIT parent_table
  OF type_name
  NOT OF
  OWNER TO new_owner
  SET TABLESPACE new_tablespace

Among them, table_constraint_using_index is:

  [ CONSTRAINT constraint_name ]
  { UNIQUE | PRIMARY KEY } USING INDEX index_name
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

partition_action is：

  ALTER DEFAULT PARTITION
  DROP DEFAULT PARTITION [IF EXISTS]
  DROP PARTITION [IF EXISTS] { partition_name | 
      FOR (RANK(number)) | FOR (value) } [CASCADE]
  TRUNCATE DEFAULT PARTITION
  TRUNCATE PARTITION { partition_name | FOR (RANK(number)) | 
      FOR (value) }
  RENAME DEFAULT PARTITION TO new_partition_name
  RENAME PARTITION { partition_name | FOR (RANK(number)) | 
      FOR (value) } TO new_partition_name
  ADD DEFAULT PARTITION name [ ( subpartition_spec ) ]
  ADD PARTITION [partition_name] partition_element
     [ ( subpartition_spec ) ]
  EXCHANGE PARTITION { partition_name | FOR (RANK(number)) | 
       FOR (value) } WITH TABLE table_name
        [ WITH | WITHOUT VALIDATION ]
  EXCHANGE DEFAULT PARTITION WITH TABLE table_name
   [ WITH | WITHOUT VALIDATION ]
  SET SUBPARTITION TEMPLATE (subpartition_spec)
  SPLIT DEFAULT PARTITION
    {  AT (list_value)
     | START([datatype] range_value) [INCLUSIVE | EXCLUSIVE] 
        END([datatype] range_value) [INCLUSIVE | EXCLUSIVE] 
        [[SEGMENT_SET segment_set_name]] }
    [ INTO ( PARTITION new_partition_name, 
             PARTITION default_partition_name ) ]
  SPLIT PARTITION { partition_name | FOR (RANK(number)) | 
     FOR (value) } AT (value) 
    [ INTO (PARTITION partition_name, PARTITION partition_name)]

partition_element is：

    VALUES (list_value [,...] )
  | START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
     [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
  | END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]

subpartition_spec is：

subpartition_element [, ...]

subpartition_element is：

   DEFAULT SUBPARTITION subpartition_name
  | [SUBPARTITION subpartition_name] VALUES (list_value [,...] )
  | [SUBPARTITION subpartition_name] 
     START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
     [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
     [ EVERY ( [number | datatype] 'interval_value') ]
  | [SUBPARTITION subpartition_name] 
     END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
     [ EVERY ( [number | datatype] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]

storage_parameter is：

   appendoptimized={TRUE|FALSE}
   blocksize={8192-2097152}
   orientation={COLUMN|ROW}
   compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE|MXCUSTOM}
   // When compresstype=MXCUSTOM, you need to specify the specific encodechain.
   encodechain={LZ4|ZSTD|DELTADELTA|DELTAZIGZAG|GORILLA|FLOATINT|SIMPLE8B|AUTO}
   /* When compresstype=MXCUSTOM and encodechain=AUTO, adaptive encoding mode can be specified at the table level:     
    * automode=1 indicates compression ratio priority, automode=2 indicates speed priority 
    */
   automode={1|2}
   compresslevel={0-9}
   fillfactor={10-100}
   oids[FALSE]

Description

ALTER TABLE modifies the definition of a table. The following describes several forms:

ADD COLUMN — Adds a new column to the table using the same semantics as CREATE TABLE. The ENCODING clause is only valid when appending to a column-store table.
DROP COLUMN [IF EXISTS] — Deletes a column from the table. Note that if you delete a table column that is used as a YMatrix database partition key, the table's partitioning strategy will change to DISTRIBUTED RANDOMLY. Indexes and table constraints involving that column will also be automatically deleted. If anything outside the table depends on that column (such as a view), you need to specify CASCADE. If IF EXISTS is specified and the column does not exist, no error is raised, but a notification is issued.
IF EXISTS — If the table does not exist, do not raise an error, but issue a notification.
SET DATA TYPE — This form changes the data type of the table's columns. Note that you cannot change the data type of columns that are used as distribution keys or partition keys. Indexes and simple table constraints involving the column are automatically converted to use the new column type by re-parsing the originally provided expression. The optional COLLATE clause specifies the collation for the new column; if omitted, the collation is the default collation for the new column type. The optional USING clause specifies how to calculate the new column value from the old value. If omitted, the default conversion is the same as the conversion from the old data type to the new data type. If there is no implicit or assigned conversion from the old type to the new type, the USING clause must be provided.
SET/DROP DEFAULT — Sets or removes the default value for a column. Default values apply only to subsequent INSERT or UPDATE commands. They do not cause changes to existing rows in the table.
SET/DROP NOT NULL — Changes whether the column is marked as allowing null values or rejecting null values. SET NOT NULL can only be used when the column does not contain null values.
SET STATISTICS — Sets the statistics collection target for each column for subsequent ANALYZE operations. The target can be set within the range of 0 to 10000, or set to -1 to use the system default statistics target (default_statistics_target). When set to 0, statistics are not collected.
SET (attribute_option = value [, ... ]) and RESET (attribute_option [, ...] )
Set or reset each attribute option. Currently, the only attribute options defined are n_distinct, n_distinct_inherited, and encodechain. The first two override the distinct value estimates made by subsequent ANALYZE operations, while encodechain is used to modify the encoding chain compression algorithm after table creation. n_distinct affects the statistics of the table itself, while n_distinct_inherited affects the statistics collected by the table and its inherited child nodes. When set to a positive value, ANALYZE assumes that the column contains exactly the specified number of distinct non-null values. When set to a negative value (must be greater than or equal to -1), ANALYZE assumes that the number of distinct non-null values in the column is linear with the table size; The exact count should be calculated by multiplying the estimated table size by the absolute value of the given number. For example, a value of -1 indicates that all values in the column are distinct, while a value of -0.5 indicates that each value appears on average twice. This is useful when the table size changes over time, as the multiplication by the number of rows in the table is not performed until query planning time. Setting the value to 0 restores the default estimation of the number of distinct values.
ADD table_constraint [NOT VALID] — Adds a new constraint to the table (not just partitions) using the same syntax as CREATE TABLE. The NOT VALID option is currently only used for foreign keys and CHECK constraints. If the constraint is marked as NOT VALID, the YMatrix database skips the potentially lengthy initial check to verify that all rows in the table satisfy the constraint. Constraints will still be enforced for subsequent inserts or updates (i.e., for foreign keys, they will fail unless there is a matching row in the referenced table; for CHECK constraints, they will fail unless the new row matches the specified check). However, the database will not assume that the constraint is valid for all rows in the table unless it is validated using the VALIDATE CONSTRAINT option. Constraint checks are skipped when creating a table, so the CREATE TABLE syntax does not include this option.
VALIDATE CONSTRAINT — This form validates previously created NOT VALID foreign key constraints by scanning the table to ensure there are no rows that do not satisfy the constraint. If the constraint has already been marked as valid, nothing happens. The advantage of separating validation from the initial creation of the constraint is that validation locks the table less than constraint creation.
ADD table_constraint_using_index — Adds a new PRIMARY KEY or UNIQUE constraint to the table based on an existing unique index. All columns in the index will be included in the constraint. The index cannot have expression columns or be a partial index. Additionally, it must be a B-tree index with a default sort order. These restrictions ensure that the index is equivalent to one created by the standard ADD PRIMARY KEY or ADD UNIQUE commands. For partitioned tables, adding a PRIMARY KEY or UNIQUE constraint to an existing unique index is not supported. If a PRIMARY KEY is specified and the columns in the index are not yet marked as NOT NULL, this command will attempt to execute ALTER COLUMN SET NOT NULL for each such column. This requires a full table scan to verify that the columns do not contain null values. In all other cases, this is a fast operation. If a constraint name is provided, the index will be renamed to match the constraint name. Otherwise, the constraint will be named the same as the index. After executing this command, the index will be “owned” by the constraint, just as when building an index using the regular ADD PRIMARY KEY or ADD UNIQUE commands. In particular, deleting the constraint will also cause the index to disappear.
Notes: If you need to add a new constraint without blocking table updates for a long time, it may be helpful to add the constraint using an existing index. To do this, create the index using CREATE INDEX CONCURRENTLY, and then install it as a formal constraint using this syntax. See the example below.
DROP CONSTRAINT [IF EXISTS] — Drops the specified constraint from the table. If IF EXISTS is specified and the constraint does not exist, no error is raised. In this case, a notification is issued.
DISABLE/ENABLE TRIGGER — Disable or enable triggers belonging to this table. Disabled triggers are still known to the system, but are not executed when their trigger events occur. For delayed triggers, the enabled status is checked at the time the event occurs, rather than when the trigger function is actually executed. You can disable or enable a single trigger specified by name, all triggers on the table, or only triggers created by the user. Disabling or enabling constraint triggers requires superuser privileges.
Notes: Triggers are not supported in the YMatrix database. Due to the parallel nature of the YMatrix database, triggers typically have very limited functionality.
CLUSTER ON/SET WITHOUT CLUSTER — Selects or removes the default index for future CLUSTER operations. It does not actually recluster the table. Notes: It is recommended not to use CLUSTER to physically reorder tables in YMatrix databases, as it takes a long time. It is better to use CREATE TABLE AS to recreate the table and sort it by the index column.
Notes: The append optimization table does not support CLUSTER ON.
SET WITHOUT OIDS — Removes the OID system column from the table.
SET ( FILLFACTOR = value) / RESET (FILLFACTOR) — Changes the fill factor of the table. The fill factor of a table is a percentage between 10 and 100. The default value is 100 (fully packed). When a smaller fill factor is specified, INSERT operations only pack the table pages to the specified percentage; the remaining space on each page is reserved for updating rows on that page. This allows UPDATE operations to place the updated copy of a row on the same page as the original copy, which is more efficient than placing it on another page. For a table where entries are never updated, full packing is the best choice, but for tables that are updated frequently, a smaller packing factor is appropriate. Note that this command does not immediately modify the table contents. You will need to rewrite the table to get the desired effect. This can be done using VACUUM or one of the ALTER TABLE forms that forces a table rewrite.
SET DISTRIBUTED — Changes the table's distribution strategy. Changing the hash distribution strategy, or changing to or from the replication strategy, will result in the physical redistribution of table data on disk, which may consume significant resources.
INHERIT parent_table / NO INHERIT parent_table — Adds or removes the target table as a child table of the specified parent table. Queries on the parent will include records from its child tables. To be added as a child, the target table must already contain all the same columns as the parent (it may also have additional columns). These columns must have matching data types, and if they have NOT NULL constraints in the parent, they must also have NOT NULL constraints in the child. For all CHECK constraints on the parent table, matching constraints must also exist on the child table, except for constraints marked as non-inheritable in the parent table (i.e., created using ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT). All matching child table constraints must not be marked as non-inheritable. UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are currently not considered, but this may change in the future.
OF type_name — This form links the table to a composite type, as if CREATE TABLE OF had already formed it. The list of column names and types for the table must exactly match the list for the composite type; the presence of the oid system column may differ. The table must not inherit from any other table. These restrictions ensure that CREATE TABLE OF will allow equivalent table definitions.
NOT OF — This form separates the typed table from its type.
OWNER — Changes the owner of a table, sequence, or view to the specified user.
SET TABLESPACE — Changes the tablespace of a table to the specified tablespace and moves the data files associated with the table to the new tablespace. Indexes in the table (if any) are not moved; however, they can be moved separately using other SET TABLESPACE commands. You can use the ALL IN TABLESPACE form to move all tables in the current database within a tablespace. This form locks all tables to be moved before moving each table. This form also supports OWNED BY, which moves only tables owned by the specified role. If the NOWAIT option is specified, the command will fail if it cannot immediately acquire all necessary locks. Note that this command does not move system catalogs; use ALTER DATABASE or explicit ALTER TABLE calls as needed. The information_schema relationships are not considered part of the system catalog and will be moved. See also CREATE TABLESPACE. If you change the tablespace of a partitioned table, all child table partitions will also be moved to the new tablespace.
RENAME — Changes the name of a table (or index, sequence, or view), the name of a single column in a table, or the name of a constraint on a table. This has no effect on the stored data. Note that YMatrix database distribution columns cannot be renamed.
SET SCHEMA — Moves a table to another schema. Associated indexes, constraints, and sequences owned by the table columns are also moved.
SET SEGMENT_SET — Modify the SEGMENT_SET object of a partitioned table.
ALTER PARTITION | DROP PARTITION | RENAME PARTITION | TRUNCATE PARTITION | ADD PARTITION | SPLIT PARTITION | EXCHANGE PARTITION | SET SUBPARTITION TEMPLATE — Modify the structure of a partitioned table. In most cases, you must traverse the parent table to change one of its child table partitions.

Notes: If you add a partition to a table with subpartition encoding, the new partition inherits the storage instructions of that subpartition.

Except for RENAME and SET SCHEMA, all ALTER TABLE forms that act on a single table can be combined into multiple change lists to be applied together. For example, you can add several columns and/or change the types of several columns in a single command. This is especially useful for large tables, as only one traversal of the table is required.

You must own the table to use ALTER TABLE. To change the table's schema or tablespace, you must also have CREATE privilege on the new schema or tablespace. To add the table as a new child of a parent table, you must also own the parent table. To change the owner, you must be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. To add columns, change column types, or use the OF clause, you must also have USAGE privilege on the data type. Superusers automatically have these privileges.

Notes: Memory usage increases significantly if the table has multiple partitions, the table is compressed, or the table has large block sizes. If the number of relationships associated with the table is large, this may force operations on the table to use more memory. For example, if the table is a CO table with a large number of columns, each column is a relationship. Operations such as ALTER TABLE ALTER COLUMN will open all columns in the table to allocate the associated buffers. If a CO table has 40 columns and 100 partitions, and these columns are compressed with a block size of 2MB (system factor of 3), the system attempts to allocate 24 GB, i.e., (40 × 100) × (2 × 3) MB or 24 GB.

Parameters

ONLY

Performs the operation only on the specified table name. If the ONLY keyword is not used, the operation will be performed on the named table and any subtable partitions associated with that table.

Notes: You can only add or drop columns, or change column types, in the parent table or child tables. The parent table and its descendants must always have the same columns and types.

name

The name of the existing table to change (possibly schema-qualified). If ONLY is specified, only that table is changed. If ONLY is not specified, the table and all its descendants (if any) are updated.

Notes: Constraints can only be added to the entire table, not to partitions. Due to this restriction, the name parameter can only contain the table name, not the partition name.

column_name

The name of the new or existing column. Please note that YMatrix database distribution columns must be handled with extreme care. Changing or deleting these columns will change the distribution policy of the table.

new_column_name

The new name of an existing column.

new_name

The new name of the table.

type

The data type of a new column or the new data type of an existing column. If you change the data type of a YMatrix distribution key column, you can only change it to a compatible type (e.g., text to varchar is allowed, but text to int is not).

table_constraint

New table constraint for the table. Note that YMatrix databases do not currently support foreign key constraints. In addition, tables are allowed only one unique constraint, and the uniqueness must be within the YMatrix database distribution key.

constraint_name

Name of the existing constraint to be dropped.

CASCADE

Automatically drops objects that depend on the dropped column or constraint (for example, views that reference the column).

RESTRICT

Refuse to delete the column or constraint if there are any related objects. This is the default behavior.

trigger_name

The name of the single trigger to disable or enable. Note that YMatrix databases do not support triggers.

ALL

Disable or enable all triggers belonging to the table, including triggers related to constraints. If any triggers are internally generated constraint triggers (e.g., triggers used to enforce foreign key constraints or deferrable uniqueness and exclusion constraints), this requires superuser privileges.

USER

Disables or enables all triggers belonging to the table, except for internally generated constraint triggers (e.g., triggers used to enforce foreign key constraints or deferrable uniqueness and exclusion constraints).

index_name

The name of the index that the table should be marked as clustered. Notes: It is recommended not to use CLUSTER to physically reorder tables in YMatrix databases, as it takes a long time. It is better to use CREATE TABLE AS to recreate the table and sort it by the index column.

FILLFACTOR

Sets the fill factor percentage for the table.

value

The new value for the FILLFACTOR parameter, a percentage between 10 and 100. The default value is 100.

DISTRIBUTED BY ({column_name [opclass]}) | DISTRIBUTED RANDOMLY | DISTRIBUTED REPLICATED

Specifies the distribution strategy for the table. Changing the hash distribution strategy will result in the physical redistribution of table data, which may consume significant resources. If the same hash distribution strategy is declared or the distribution is changed from hash to random, data will not be redistributed unless SET WITH (REORGANIZE=true) is declared. Changing to a replicated distribution strategy or changing from a replicated distribution strategy will result in the redistribution of table data.

REORGANIZE=true|false

Use REORGANIZE=true when the hash distribution strategy is not changed or when changing from hash to random distribution, and you always want to redistribute the data.

parent_table

The parent table to associate or dissociate with this table.

new_owner

The role name of the new owner of the table.

new_tablespace

The name of the tablespace to which the table will be moved.

new_schema

The name of the schema to which the table will be moved.

parent_table_name

The name of the top-level parent table when modifying a partitioned table.

ALTER [DEFAULT] PARTITION

If the partition to be modified is deeper than the first-level partition, use the ALTER PARTITION clause to specify which subpartition in the hierarchy to modify.

DROP [DEFAULT] PARTITION

Deletes the specified partition. If the partition has subpartitions, they are also automatically deleted.

TRUNCATE [DEFAULT] PARTITION

Truncates the specified partition. If the partition has subpartitions, they are also automatically truncated.

RENAME [DEFAULT] PARTITION

Change the partition name (not the relation name). Partitioned tables are created using the following naming convention: \<parentname>_\<level>_prt_\<partition_name>.

ADD DEFAULT PARTITION

Add a default partition to an existing partition design. When data does not match an existing partition, it is inserted into the default partition. A partition design without a default partition will reject incoming rows that do not match existing partitions. A name must be specified for the default partition.

ADD PARTITION

partition_element - Use the existing partition type of the table (range or list) to define the boundaries of the new partition to be added.
name - The name of this new partition.
VALUES - For list partitions, define the values that the partition will contain.
START - For range partitions, defines the starting range value of the partition. By default, the starting value is INCLUSIVE. For example, if you declare the start date as “2016-01-01,” the partition will include all dates greater than or equal to “2016-01-01.” Typically, the data type of the START expression is the same as the type of the partition key column. If this is not the case, you must explicitly convert it to the expected data type.
END - For range partitioning, defines the end range value of the partition. By default, the end value is EXCLUSIVE. For example, if you declare the end date as “2016-02-01,” the partition will include all dates less than but not equal to “2016-02-01.” Typically, the data type of the END expression is the same as the type of the partition key column. If this is not the case, you must explicitly convert it to the expected data type.
WITH - Sets the storage options for the partitioned table. For example, you may want to store older partitions as append-optimized tables and newer partitions as regular heap tables. For details on storage options, see CREATE TABLE.
TABLESPACE - The name of the tablespace in which to create the partitioned table.
subpartition_spec - A partition design that can only be created when there is no subpartition template. Specifies the subpartition specification for the new partition to be added. If the partitioned table was originally defined using a subpartition template, that template will be used to automatically generate subpartitions.
SEGMENT_SET - Specifies the SEGMENT_SET object for the new partition to be added.

EXCHANGE [DEFAULT] PARTITION

Exchanges another table into the partition hierarchy, replacing the position of an existing partition. In a multi-level partition design, you can only exchange the lowest-level partition (the partition containing data).
The YMatrix database server configuration parameter gp_enable_exchange_default_partition controls the availability of the EXCHANGE DEFAULT PARTITION clause. The default value of this parameter is off. If this clause is specified in the ALTER TABLE command, the YMatrix database will return an error.
Notes: Before swapping the default partition, you must ensure that the data in the table to be swapped (the new default partition) is valid for the default partition. For example, the data in the new default partition must not contain data that is valid in other leaf partitions of the partitioned table. Otherwise, partitioned table queries with swapped default partitions performed by GPORCA may return incorrect results.
WITH TABLE table_name - The name of the table you want to swap into the partition design. You can swap a table where the table data is stored in the database. For example, the table was created using the CREATE TABLE command. The table must have the same number of columns, column order, column names, column types, and distribution strategy as the parent table.
Using the EXCHANGE PARTITION clause, you can also swap a readable external table (created using the CREATE EXTERNAL TABLE command) into the partition hierarchy instead of an existing leaf subpartition. If you specify a readable external table, you must also specify the WITHOUT VALIDATION clause to skip table validation for the CHECK constraints of the partition being swapped.
If the partitioned table contains columns with check constraints or NOT NULL constraints, swapping leaf subpartitions with external tables is not supported.
You cannot swap partitions with replicated tables. Swapping partitions with partitioned tables or subpartitions of partitioned tables is not supported. WITH | WITHOUT VALIDATION - Validates whether the data in the table matches the CHECK constraints of the partition you want to swap. The default setting is to validate the data based on the CHECK constraints. Notes: If you specify the WITHOUT VALIDATION clause, you must ensure that the data in the table being swapped for existing subleaf partitions is valid for the CHECK constraints on the partition.

SET SUBPARTITION TEMPLATE

Modify the subpartition template for an existing partition. After setting a new subpartition template, all newly added partitions will have the new subpartition design (existing partitions will not be modified).

SPLIT DEFAULT PARTITION

Split the default partition. In multi-level partitions, only range partitions can be split, not list partitions, and only the lowest-level default partition (the partition containing data) can be split. Splitting the default partition creates a new partition containing the specified value and retains the default partition, which includes any values that do not match the existing partition.
AT - For list partitioned tables, specify a list value to be used as the split condition.
START - For range partitioned tables, specify the starting value for the new partition.
END - For range partitioned tables, specify the end value of the new partition.
INTO - Allows you to specify a name for the new partition. When splitting the default partition using the INTO clause, the specified second partition name must always be the name of the existing default partition. If you do not know the name of the default partition, you can use the pg_partitions view to find it.

SPLIT PARTITION

Splits an existing partition into two partitions. In multi-level partitions, only range partitions can be split, not list partitions, and only the lowest-level partitions (those containing data) can be split.
AT - Specifies a single value to be used as the split condition. The partition will be split into two new partitions, with the specified split value serving as the starting range for the latter partition.
INTO - Allows the user to specify names for the two new partitions created by the split.

partition_name

The name of the given partition.

FOR (RANK(number))

For range partitions, the rank of the partition within the range.

FOR (‘value’)

Specifies a partition by declaring a value that falls within the partition boundary specification. If the value declared with FOR matches both a partition and one of its subpartitions (e.g., if the value is a date and the table is partitioned first by month and then by day), FOR operates at the first level where a match is found (e.g., the monthly partition). If the user's intention is to operate on a subpartition, the statement must be declared as follows: ALTER TABLE name ALTER PARTITION FOR (‘2016-10-01’) DROP PARTITION FOR (‘2016-10-01’);

Notes

The table name specified in the ALTER TABLE command cannot be a partition name within a table.

Be especially careful when modifying or deleting columns that are part of the distribution key for a YMatrix database, as this may alter the table's distribution strategy.

YMatrix databases currently do not support foreign key constraints. For unique constraints to be enforced in a YMatrix database, the table must be hash-distributed (not distributed randomly), and all distribution key columns must match the leading columns of the unique constraint.

Adding CHECK or NOT NULL constraints requires scanning the table to verify that existing rows comply with the constraints.

When adding a column using ADD COLUMN, all existing rows in the table are initialized with the column's default value. If the DEFAULT clause is not specified, the value is initialized to NULL. Adding a column with a non-null default value or changing the type of an existing column will require rewriting the entire table and indexes. As an exception, if the USING clause does not change the column's content, and the old type can be coerced to the new type or the new type is unrestricted, the table does not need to be rewritten, but any indexes on the affected column must still be rebuilt. Adding or removing system oid columns also requires rewriting the entire table. For large tables, rebuilding the table and indexes may take a significant amount of time and temporarily require up to twice the disk space.

You can specify multiple changes in a single ALTER TABLE command, which will be applied to the table in one go.

The DROP COLUMN statement does not physically delete the column but simply makes it invisible to SQL operations. Subsequent inserts and updates to the table will store a null value for that column. Therefore, deleting a column is fast but does not immediately reduce the table's disk size, as the space occupied by the deleted column is not reclaimed. As existing rows are updated, space will be reclaimed over time. However, if you delete a system OID column, the table will be immediately rewritten.

To force immediate reclamation of the space occupied by the deleted column, you can execute a form of ALTER TABLE to rewrite the entire table. This will result in the reconstruction of each row and the replacement of the deleted column with a null value.

The rewrite form of ALTER TABLE is not MVCC-safe. After the table is rewritten, if concurrent transactions are using a snapshot taken before the rewrite occurred, the table will appear empty to those concurrent transactions.

The USING option of SET DATA TYPE can actually specify any expression involving the old value of the row. That is, it can reference other columns as well as the column to be converted. This allows for very general conversions using the SET DATA TYPE syntax. Due to this flexibility, the USING expression is not applied to the column's default value (if any); the result may not be the constant expression required for the default value. This means that when there is no implicit or assignment conversion from the old type to the new type, SET DATA TYPE may not be able to convert the default value even if a USING clause is provided. In this case, use DROP DEFAULT to remove the default value, execute ALTER TYPE, and then use SET DEFAULT to add the appropriate new default value. Similar considerations apply to indexes and constraints involving the column.

If a table is partitioned or has any descendants, you cannot add, rename, or change the type of a column or rename an inherited constraint in the parent table without performing the same operation on the descendants. This ensures that descendants always have columns that match those of the parent.

To view the structure of a partitioned table, you can use the pg_partitions view. This view can help you identify specific partitions you may want to modify.

A recursive DROP COLUMN operation will only delete columns from child tables if the columns are not inherited from any other parent and have never been independently defined. A non-recursive DROP COLUMN (only ALTER TABLE ONLY ... DROP COLUMN) never deletes any child columns; instead, it marks them as independently defined rather than inherited.

TRIGGER, CLUSTER, OWNER, and TABLESPACE operations never recursively affect descendant tables. That is, they always behave as specified by ONLY. Only CHECK constraints that are not marked as NO INHERIT are reapplied when adding constraints.

These ALTER PARTITION operations are supported if the data on the partitioned table containing leaf partitions that have been swapped to use external tables has not changed. Otherwise, an error is returned.

Adding or dropping a column.
Changing the data type of a column.

Partitioned tables do not support these ALTER PARTITION operations if the partitioned table contains leaf partitions that have been swapped to use external tables:

Setting a subpartition template.
Altering the partition properties.
Creating a default partition.
Setting a distribution policy.
Setting or dropping a NOT NULL constraint on a column.
Adding or dropping constraints.
Splitting an external partition.

No changes are allowed to any part of the system catalog tables.

YMatrix supports modifying the encoding chain compression algorithm using SQL statements after the table is created.

Example

Add a column to a table:

ALTER TABLE distributors ADD COLUMN address varchar(30);

Rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

Rename an existing table:

ALTER TABLE distributors RENAME TO suppliers;

Add a non-null constraint to a column:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

Rename an existing constraint:

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

Add a check constraint to a table and all its child tables:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK 
(char_length(zipcode) = 5);

To add a check constraint only to the table and not its child tables, perform the following:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

(The check constraint will also not be inherited by future child tables.)

Remove a check constraint from a table and all its child tables:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

To remove the check constraint from only one table:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(The check constraint still exists for any child tables that inherit from distributors.)

To move the table to a different schema:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

Change the distribution strategy of a table to replicated:

ALTER TABLE myschema.distributors SET DISTRIBUTED REPLICATED;

Modify the SEGMENT_SET object of a partitioned table that has been created:

## Create SEGMENT_SET objects ss1 and ss2 in a three-node cluster
CREATE SEGMENT_SET ss1 SEGMENTS(‘0,2’);
CREATE SEGMENT_SET ss2 SEGMENTS(‘1,2’);

## Create regular table t1 and partitioned table t2
CREATE TABLE t1(a int, b int) DISTRIBUTED BY(a) SEGMENT_SET ss1;
CREATE TABLE t2(a int, b int) DISTRIBUTED BY(a) PARTITION BY RANGE(b) (DEFAULT PARTITION others SEGMENT_SET ss1);

# Modify the SEGMENT_SET object of the regular table t1.
ALTER TABLE t1 SET SEGMENT_SET ss2;

## Modify the SEGMENT_SET object of the partitioned table t2. The SEGMENT_SET of all subpartitions of t2 will also be modified.
ALTER TABLE t2 SET SEGMENT_SET ss2;

Modify the SEGMENT_SET of a specific partition:

ALTER TABLE t2_prt_1_others SET SEGMENT_SET ss1;
ALTER TABLE t2 ALTER DEFAULT PARTITION SET SEGMENT_SET ss1;

Specify the SEGMENT_SET object for a new partition:

ALTER TABLE t2 ADD PARTITION START(6) END(10) SEGMENT_SET ss2;

Add a new partition to a partitioned table:

ALTER TABLE sales ADD PARTITION 
            START (date ‘2017-02-01’) INCLUSIVE 
            END (date ‘2017-03-01’) EXCLUSIVE;

Add a default partition to an existing partitioned design:

ALTER TABLE sales ADD DEFAULT PARTITION other;

Rename a partition:

ALTER TABLE sales RENAME PARTITION FOR (‘2016-01-01’) TO 
jan08;

Delete the first (oldest) partition in the range sequence:

ALTER TABLE sales DROP PARTITION FOR (RANK(1));

Swap the table into the user's partition design:

ALTER TABLE sales EXCHANGE PARTITION FOR (‘2016-01-01’) WITH 
TABLE jan08;

Split the default partition (existing default partition name other) to add new monthly partitions for January 2017:

ALTER TABLE sales SPLIT DEFAULT PARTITION 
START (‘2017-01-01’) INCLUSIVE 
END (‘2017-02-01’) EXCLUSIVE 
INTO (PARTITION jan09, PARTITION other);

Split the monthly partition into two partitions, with the first partition containing dates from January 1 to 15, and the second partition containing dates from January 16 to 31:

ALTER TABLE sales SPLIT PARTITION FOR (‘2016-01-01’)
AT (‘2016-01-16’)
INTO (PARTITION jan081to15, PARTITION jan0816to31);

To recreate the primary key constraint without blocking updates during index rebuild:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

Modify the table-level compression algorithm to adaptive encoding:

ALTER TABLE t SET (encodechain=auto);

Compatibility

ADD (without USING INDEX), DROP, SET DEFAULT, and SET DATA TYPE (without USING) comply with the SQL standard. Other forms are SQL standard extensions of the YMatrix database. Similarly, the ability to specify multiple operations in a single ALTER TABLE command is also an extension.

ALTER TABLE DROP COLUMN can be used to delete the only column in a table while retaining zero columns. This is an extension to SQL, which does not allow zero columns.