Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ SQL Syntax/ A/ ALTER TABLE

Updated on 2024-08-20 GMT+08:00

View PDF

ALTER TABLE

Description

Modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level security policies, and adding or updating multiple columns.

Precautions

If the base table is a hash-distributed table, to create a primary key or unique index that does not contain the distribution key of the base table, use a GSI (by specifying the BY GLOBAL INDEX field); to create a primary key or unique index that contains the distribution key of the base table, use a common index (without specifying the BY GLOBAL INDEX field). In single-DN deployment mode, both the GSI and common index can be successfully created. If the base table is a non-hash-distributed table, you can only create the primary key or unique index as a common index. That is, the index key must contain the distribution key of the base table.
The owner of a table, users granted with the ALTER permission on the table, or users granted with the ALTER ANY TABLE permission can run the ALTER TABLE command. The system administrator has the permission to run the command by default. To modify the owner or schema of a table, you must be the table owner or system administrator and a member of the new owner role.
The tablespace of a partitioned table cannot be modified, but the tablespace of the partition can be modified.
The storage parameter ORIENTATION cannot be modified.
Currently, SET SCHEMA can only set schemas to user schemas. It cannot set a schema to a system internal schema.
The distribution key (or column) of a table cannot be modified.
The partition key column of a partitioned table cannot be changed or the character set cannot be converted.

Auto-increment columns cannot be added, or a column whose DEFAULT value contains the NEXTVAL() expression cannot be added.
Row-level security cannot be enabled for foreign tables and temporary tables.
When you delete a PRIMARY KEY constraint by constraint name, the NOT NULL constraint is not deleted. If necessary, manually delete the NOT NULL constraint.
When JDBC is used, the DEFAULT value can be set through PrepareStatement.
If you add a column using ADD COLUMN, all existing rows in the table are initialized to the column's default value (NULL if no DEFAULT value is specified).
If no DEFAULT value is specified for the new column, NULL is used, and no full table update is triggered.

If the new column has the DEFAULT value, the column must meet all the following requirements. Otherwise, the entire table is updated, leading to additional overheads and affecting online services.

1. The data type is BOOL, BYTEA, SMALLINT, BIGINT, SMALLINT, INTEGER, NUMERIC, FLOAT, DOUBLE PRECISION, CHAR, VARCHAR, TEXT, TIMESTAMPTZ, TIMESTAMP, DATE, TIME, TIMETZ, or INTERVAL.

2. The length of the DEFAULT value of the added column cannot exceed 128 bytes.

3. The DEFAULT value of the added column does not contain the volatile function.

4. The DEFAULT value is required and cannot be NULL.

If you are not sure whether condition 3 is met, check whether the provolatile attribute of the function in the PG_RPOC system catalog is v.
If you add a row-level expression when adding or changing an ILM policy for a data object, note that the row-level expression supports only the functions listed in the whitelist. For details about the whitelist function list, see Row Expression Function Whitelist.
The number of table constraints cannot exceed 32,767.

Syntax

Modify the definition of a table.

      
       
         
         
           ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
ALTER TABLE [ IF EXISTS ] table_name
    ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
ALTER TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
ALTER TABLE [ IF EXISTS ] table_name
    RENAME [TO | AS | = ] new_table_name;
RENAME {TABLE | TABLES} {table_name TO new_table_name} [, ...];
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME [ COLUMN ] column_name TO new_column_name;
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME CONSTRAINT constraint_name TO new_constraint_name;
ALTER TABLE [ IF EXISTS ] table_name
    SET SCHEMA new_schema;
ALTER TABLE [ IF EXISTS ] table_name GSIWAITALL;

          

        

      
     

The table operation ACTION can be one of the following clauses:

       
        
          
          
            column_clause
    | ADD table_constraint [ NOT VALID ]
    | ADD table_constraint_using_index
    | VALIDATE CONSTRAINT constraint_name
    | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    | DROP PRIMARY KEY
    | CLUSTER ON index_name
    | SET WITHOUT CLUSTER
    | SET ( {storage_parameter = value} [, ... ] )
    | RESET ( storage_parameter [, ... ] )
    | OWNER TO new_owner
    | SET TABLESPACE new_tablespace
    | SET { COMPRESS | NOCOMPRESS }
    | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
    | ADD NODE ( nodename [, ... ] )
    | DELETE NODE ( nodename [, ... ] )
    | UPDATE SLICE LIKE table_name
    | DISABLE TRIGGER [ trigger_name | ALL | USER ]
    | ENABLE TRIGGER [ trigger_name | ALL | USER ]
    | ENABLE REPLICA TRIGGER trigger_name
    | ENABLE ALWAYS TRIGGER trigger_name
    | ENABLE ROW LEVEL SECURITY
    | DISABLE ROW LEVEL SECURITY
    | FORCE ROW LEVEL SECURITY
    | NO FORCE ROW LEVEL SECURITY
    | ENCRYPTION KEY ROTATION
    | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
    | AUTO_INCREMENT [ = ] value
    | COMMENT [ = ] 'string'
    | [ [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]
    | CONVERT TO CHARACTER SET | CHARSET charset [ COLLATE collation ]
    | ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]
    | [ MODIFY { PARTITION | SUBPARTITION }] ILM { ENABLE | DISABLE | DELETE } POLICY policy_name
    | [ MODIFY { PARTITION | SUBPARTITION }] ILM { ENABLE_ALL | DISABLE_ALL | DELETE_ALL}

           

         

       
      

ADD table_constraint [ NOT VALID ]
Adds a table constraint.
ADD table_constraint_using_index
Adds a primary key constraint or unique constraint to a table based on the existing unique index. If the specified index is a GSI, an error is reported. In this case, you need to use the BY GLOBAL INDEX syntax to add a GSI index constraint.
VALIDATE CONSTRAINT constraint_name
Validates a check-class constraint created with the NOT VALID option, and scans the entire table to ensure that all rows meet the constraint. Nothing happens if the constraint is already marked valid.
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
Deletes a table constraint.
DROP PRIMARY KEY
Deletes a primary key constraint from a table. This syntax is valid only when sql_compatibility is set to 'MYSQL'.
CLUSTER ON index_name
Selects the default index for future CLUSTER operations. Actually, the table is not re-clustered.
SET WITHOUT CLUSTER
Deletes the most recently used CLUSTER index from the table. Deletes the most recently used CLUSTER index from the table.
SET ( {storage_parameter = value} [, ... ] )
Changes one or more storage parameters for the table. If the value of table_name is an index name, ACTIVE_PAGES specifies the number of index pages, which may be less than the actual number of physical file pages and can be used for optimizer optimization. Currently, this parameter is valid only for the local index of the Ustore partitioned table and will be updated by VACUUM (including AUTOVACUUM) and ANALYZE. You are advised not to manually set this parameter because it is invalid in distributed mode.
RESET ( storage_parameter [, ... ] )
Resets one or more storage parameters to their defaults. As with SET, a table rewrite might be needed to update the table entirely.
OWNER TO new_owner
Changes the owner of a table, sequence, or view to the specified user.
SET TABLESPACE new_tablespace
Changes the table's tablespace to the specified tablespace and moves the data files associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE option in ALTER INDEX.
SET { COMPRESS | NOCOMPRESS }
Sets the compression feature of a table. The table compression feature affects only the storage mode of data inserted in a batch subsequently and does not affect storage of existing data. Setting the table compression feature will result in the fact that there are both compressed and uncompressed data in the table. Row-store tables do not support compression.
TO { GROUP groupname | NODE ( nodename [, ... ] ) }
The syntax is only available in extended mode (when GUC parameter support_extended_features is on). Exercise caution when enabling the mode. It is mainly used for tools like internal dilatation tools. Common users should not use the mode. This command only modifies the logical mapping relationship of the table distribution nodes and does not migrate the table's metadata and data on the DN.
ADD NODE ( nodename [, ... ] )
It is only available for internal scale-out tools. Common users should not use the syntax.
DELETE NODE ( nodename [, ... ] )
It is only available for internal scale-in tools. Common users should not use the syntax.
UPDATE SLICE LIKE table_name
This syntax is used by internal scaling tools and cannot be used by common users.
DISABLE TRIGGER [ trigger_name | ALL | USER ]
Disables a single trigger specified by trigger_name, disables all triggers, or disables only user triggers (excluding internally generated constraint triggers, for example, deferrable unique constraint triggers and exclusion constraints triggers).

Exercise caution when using this function because data integrity cannot be ensured as expected if the triggers are not executed.
| ENABLE TRIGGER [ trigger_name | ALL | USER ]
Enables a single trigger specified by trigger_name, enables all triggers, or enables only user triggers.
| ENABLE REPLICA TRIGGER trigger_name
Determines that the trigger firing mechanism is affected by the configuration variable session_replication_role. When the replication role is origin (default value) or local, a simple trigger is fired.

When ENABLE REPLICA is configured for a trigger, it is triggered only when the session is in replica mode.
| ENABLE ALWAYS TRIGGER trigger_name
Determines that all triggers are fired regardless of the current replica mode.
| { DISABLE | ENABLE } [ REPLICA | ALWAYS ] RULE
Enables or disables a rule for tables. Disabled rules are still visible in the system, but are not applied during query rewriting. The ON SELECT rule cannot be disabled because it is related to the view implementation. Rules configured as ENABLE REPLICA are enabled only when the session is in replica mode, while those configured as ENABLE ALWAYS can be enabled regardless of the replica mode. Rule triggering is also affected by configuration variable session_replication_role, which is similar to the preceding trigger setting.
| { DISABLE | ENABLE } ROW LEVEL SECURITY
Enables or disables row-level security for a table.

If row-level security is enabled for a data table but no row-level security policy is defined, the row-level access to the data table is not affected. If row-level security for a table is disabled, the row-level access to the table is not affected even if a row-level security policy has been defined. For details, see CREATE ROW LEVEL SECURITY POLICY.
| {NO FORCE|FORCE} ROW LEVEL SECURITY
Forcibly enables or disables row-level security for a table.

By default, the table owner is not affected by the row-level security feature. However, if row-level security is forcibly enabled, the table owner (excluding system administrators) will be affected. System administrators are not affected by any row-level security policies.
| ENCRYPTION KEY ROTATION
Rotation of the transparent data encryption key.

The data encryption key rotation of a table can be performed only when the TDE function is enabled for the database and enable_tde of the table is set to on. After the key rotation operation is performed, the system automatically applies for a new KMS key. After the key rotation, the data encrypted using the old key is decrypted using the old key, and the newly written data is encrypted using the new key. To ensure the security of encrypted data, you can periodically update the key based on the amount of new data in the encrypted table. It is recommended that the key be updated every two to three years.

REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
Specifies the record level of old tuples in UPDATE and DELETE statements on a table in logical replication scenarios.
- DEFAULT records the old value of the primary key column. If there is no primary key, DEFAULT does not record the old value.
- USING INDEX records the old values of columns covered by the named indexes. These values must be unique, non-local, and non-deferrable, and contain the values of columns marked NOT NULL.
- FULL records the old values of all columns in the row.
- NOTHING does not record information in old rows.
In logical replication scenarios, when the UPDATE and DELETE statements of a table are parsed, the parsed old tuples consist of the information recorded in this method. For tables with primary keys, this option can be set to DEFAULT or FULL. For a table without a primary key, set this parameter to FULL. Otherwise, the old tuple will be parsed as empty during decoding. You are advised not to set this parameter to NOTHING in common scenarios because old tuples are always parsed as empty.

For Ustore tables, the NOTHING option is invalid, and the actual effect is the same as that of FULL. If DEFAULT does not have a primary key, all columns in the row are recorded.
AUTO_INCREMENT [ = ] value
Sets the next auto-increment value of the auto-increment column. The configured value takes effect only when it is greater than the current auto-increment counter.

The value must be a non-negative integer and cannot be greater than 2¹²⁷ – 1.

This clause takes effect only when sql_compatibility is set to 'MYSQL'.
[ [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]
Modifies the default character set and default collation of a table to the specified values. The modification does not affect the existing columns in the table.
CONVERT TO CHARACTER SET | CHARSET charset [ COLLATE collation ]
Modifies the default character set and default collation of a table to the specified values, sets the character set and collation of all columns with character type to the specified value and converts the data in the column to new character set encoding.
ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]
Adds an ILM policy to a table. A complete ILM policy consists of two parts: ILM action and ILM condition. The ILM action is used to define a specific data compression or movement behavior. The ILM condition is used to define a condition for triggering the ILM action. The ILM condition is a row-level condition, that is, when the ILM condition applies to each row in the heap table and the current row is not modified within a period of time, the ILM condition is met, and the ILM action is triggered. EXPR supports only basic operation functions (such as to_date and substr) of table columns and types.
[ MODIFY { PARTITION | SUBPARTITION }] ILM { ENABLE | DISABLE | DELETE } POLICY policy_name
Modifies a single ILM policy of a table. policy_name is the value of POLICY_NAME queried from the system view GS_ADM_ILMOBJECTS or GS_MY_ILMOBJECTS.
[ MODIFY { PARTITION | SUBPARTITION }] ILM { ENABLE_ALL | DISABLE_ALL | DELETE_ALL}
Modifies all ILM policies of a table.

The column_clause can be one of the following clauses:

        
         
           
           
             ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]    
| MODIFY column_name data_type    
| MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
| MODIFY column_name [ CONSTRAINT constraint_name ] NULL
| MODIFY column_name data_type { { [ COMMENT 'string' ] [ ... ] } | AUTO_INCREMENT }
| MODIFY [ COLUMN ] column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ]
| CHANGE [ COLUMN ] column_name new_column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ]
| DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]    
| ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]    
| ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }    
| ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL    
| ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer    
| ADD STATISTICS (( column_1_name, column_2_name [, ...] ))    
| DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))    
| ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )    
| ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )    
| ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

            

          

        
       

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ CHARACTER SET | CHARSET [ = ] charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
Adds a column to a table. If a column is added using ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). When IF NOT EXISTS is specified and columns with the same name exist, a notice is returned, indicating that the column already exists. When IF NOT EXISTS is not specified and columns with the same name exist, an error is returned.
ADD ( { column_name data_type [ compress_mode ] } [, ...] )
Adds columns in the table.
MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] )
Modifies the data type of an existing column in the table. Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.
MODIFY column_name data_type { { [ COMMENT 'string' ] [ ... ] } | AUTO_INCREMENT }
- Replaces the definition of existing column in a table with a new definition. The indexes and independent object constraints, such as primary keys, unique key and CHECK constraints in the previous column are not deleted.
- This syntax can be used only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
- This syntax cannot be used for foreign tables, or modifying encrypted columns.
- The data type and collation rule of partition key columns cannot be modified. The data type and collation rule of columns referenced by rules and materialized views cannot be modified.
- If a column whose data type or collation rule is modified is referenced by a generated column, the data in the generated column is regenerated.
- If some objects (such as indexes, independent object constraints, views, triggers, and row-level security policies) depend on a modified column, these objects are rebuilt during column modification. If the definition of the modified column violates the constraints of this type of object, the modification fails. For example, the data type of the column that is used as the view result column cannot be modified. Pay attention on the failure impact before modification.
- In HASH, LIST, and RANGE distribution scenarios, the data type and comments of distribution keys cannot be modified.
- If a modified column is called by some objects (such as user-defined functions and stored procedures), the modified column does not process these objects. After the column names are modified, these objects may be unavailable. Please evaluate the impact before modification.
- The syntax of this clause is the same as that of MODIFY column_name data_type in the previous clause, but the semantic function is different. When the GUC parameter b_format_behavior_compat_options contains the enable_modify_column option, the function of this clause is used.
- Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.
MODIFY [ COLUMN ] column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ]
Replaces the definition of existing field in a table with a new definition. The indexes and independent object constraints, such as primary keys, unique key and CHECK constraints in the previous field are not deleted.

This syntax can be used only when sql_compatibility is set to 'MYSQL'. Foreign tables are not supported. Encrypted columns cannot be modified. The data type and collation rule of partition key columns cannot be modified. The data type and collation rule of columns referenced by rules and materialized views cannot be modified.

If a column whose data type or collation rule is modified is referenced by a generated column, the data in the generated column is regenerated.

If some objects (such as indexes, independent object constraints, views, triggers, and row-level security policies) depend on a modified column, these objects are rebuilt during column modification. If the definition of the modified column violates the constraints of this type of object, the modification fails. For example, the data type of the column that is used as the view result column cannot be modified. Pay attention on the failure impact before modification.

If a modified column is called by some objects (such as user-defined functions and stored procedures), the modified column does not process these objects. After the columns are modified, these objects may be unavailable. Please evaluate the impact before modification.

Changing the character set or collation of a column converts the data in the column to the new character set for encoding.

The syntax of this clause is the same as that of MODIFY column_name data_type in the previous clause, but the semantic function is different. When the GUC parameter b_format_behavior_compat_options contains the enable_modify_column option, the function of this clause is used.

Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.
CHANGE [ COLUMN ] column_name new_column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ]
Replaces the definition and name of existing column in a table with a new definition and name. The new column name must be different from the previous. The indexes and independent object constraints, such as primary keys, unique key and CHECK constraints in the previous column are not deleted.

This syntax can be used only when sql_compatibility is set to 'MYSQL'. Foreign tables are not supported. Encrypted columns cannot be modified. The data type and collation rule of partition key columns cannot be modified. The data type and collation rule of columns referenced by rules and materialized views cannot be modified.

If a column whose data type or collation rule is modified is referenced by a generated column, the data in the generated column is regenerated.

If some objects (such as indexes, independent object constraints, views, triggers, and row-level security policies) depend on a modified column, these objects are rebuilt during column modification. If the definition of the modified column violates the constraints of this type of object, the modification fails. For example, the data type of the column that is used as the view result column cannot be modified. Pay attention on the failure impact before modification.

If a modified column is called by some objects (such as user-defined functions and stored procedures), the modified column does not process these objects. After the column names are modified, these objects may be unavailable. Please evaluate the impact before modification.

Modifying the character set or collation of a column converts the data in the column to the new character set for encoding.
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
Drops a column from a table. Indexes and constraints related to the column are automatically dropped. If an object not belonging to the table depends on the column (for example, view), CASCADE must be specified.

The DROP COLUMN statement does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent INSERT and UPDATE operations in the table will store a NULL value for the column. Therefore, column deletion takes a short period of time but does not immediately release the tablespace on the disks, because the space occupied by the deleted column is not recycled. The space will be recycled when VACUUM is executed.
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
Modifies the type of a column in a table. Indexes and simple table constraints on the column will automatically use the new data type by reparsing the originally supplied expression.

If the original data type of a column and the modified data type are binary compatible, you do not need to rewrite the entire table when running this statement. In other scenarios, the entire table is rewritten. You can check whether the original type and target type are binary compatible in the PG_CAST system catalog. If castmethod is b, they are binary compatible. For example, if the data type of the source table is text and is converted to int, table rewriting is triggered. If it is converted to clob, table rewriting is not triggered. If table rewriting is triggered, the deleted space on the table is recycled immediately.

Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.
ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }
Sets or removes the default value for a column. The default values only apply to subsequent INSERT operations; they do not cause rows already in the table to change. Defaults can also be created for views, in which case they are inserted into INSERT statements on the view before the view's ON INSERT rule is applied.
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
Changes whether a column is marked to allow null values or to reject null values. You can only use SET NOT NULL when the column contains no null values.
ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer
Specifies the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range from 0 to 10000. Set it to –1 to revert to using the default system statistics target.
{ADD | DELETE} STATISTICS ((column_1_name, column_2_name [, ...]))
Adds or deletes the declaration of collecting multi-column statistics to collect multi-column statistics as needed when ANALYZE is performed for a table or a database. The statistics about a maximum of 32 columns can be collected at a time. You are not allowed to add or delete such declaration for system catalogs or foreign tables.
ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

Sets or resets per-attribute options.

Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited. n_distinct affects statistics of a table, while n_distinct_inherited affects the statistics of the table and its subtables. Currently, only SET/RESET n_distinct is supported, and SET/RESET n_distinct_inherited is forbidden.
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
Sets the storage mode for a column. It specifies whether this column is held inline or in an attached table, and whether the data should be compressed. SET STORAGE does not change a table. It only specifies the recommended strategy for future table updates.

column_constraint is as follows:

          
               [ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL |
      CHECK ( expression ) |
      DEFAULT default_expr  |
      ON UPDATE update_expr |
      GENERATED ALWAYS AS ( generation_expr ) [STORED] |
      AUTO_INCREMENT |
      COMMENT 'string' |
      UNIQUE [KEY] index_parameters |
      PRIMARY KEY index_parameters |
      ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL |  MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE action ] [ ON UPDATE action ] }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters is as follows:

            
                 [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ]

update_expr is as follows:

{ CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

compress_mode of a column is as follows:

          
               { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }

table_constraint_using_index used to add the primary key constraint or unique constraint based on the unique index is as follows:

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

table_constraint is as follows:

        
             [ CONSTRAINT [ constraint_name ] ]
    { CHECK ( expression ) |
      UNIQUE [ idx_name ][ USING method ] ( { {column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters |
      PRIMARY KEY [ USING method ] ( {column_name [ ASC | DESC ] } [, ... ] ) index_parameters 
      }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    { [ COMMENT 'string' ] [ ... ] }

index_parameters is as follows:

         
              [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ][BY GLOBAL INDEX]

If index_parameters is set to BY GLOBAL INDEX, the global secondary index is used to create constraints.

Rename a table. The renaming does not affect stored data.
1 2

ALTER TABLE [ IF EXISTS ] table_name RENAME [ TO | AS | = ] new_table_name;
If you specify this parameter in a version 5.7 MySQL-compatible database (sql_compatibility set to 'MYSQL', b_format_version set to '5.7', and b_format_dev_version set to 's2'), the following situations may occur:
- If the character string corresponding to the new table name starts with "#mysql50#" and is followed by other characters, "#mysql50#" will be ignored.
- If the old and new table names are the same, no error is reported.

Rename the specified column in the table.

     
          ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    RENAME [ COLUMN ] column_name TO new_column_name;

Rename the constraint of the table.

     
          ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    RENAME CONSTRAINT constraint_name TO new_constraint_name;

Set the schema of the table.
1 2

ALTER TABLE [ IF EXISTS ] table_name SET SCHEMA new_schema;
- The schema setting moves the table into another schema. Associated indexes and constraints owned by table columns are migrated as well. Currently, the schema for sequences cannot be changed. If the table has sequences, delete the sequences, and create them again or delete the ownership between the table and sequences. In this way, the table schema can be changed.
- To change the schema of a table, you must also have the CREATE permission on the new schema. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have the CREATE permission on the table's schema. These restrictions enforce that the user can only rebuild and delete the table. A system administrator can alter the ownership of any table anyway.
- All the actions except for RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns or alter the type of several columns in a single statement. This is useful with large tables, since only one pass over the tables need be made.
- Adding a CHECK or NOT NULL constraint will scan the table to validate that existing rows meet the constraint.
- Adding a column with a non-NULL default or changing the type of an existing column will rewrite the entire table. Rewriting a large table may take much time and temporarily needs doubled disk space.

Add columns.

     
          ALTER TABLE [ IF EXISTS ] table_name
    ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );

Update columns.

     
          ALTER TABLE [ IF EXISTS ] table_name 
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );

Commit all DML transactions in the table when the SQL statement is executed.
1

ALTER TABLE [ IF EXISTS ] table_name GSIWAITALL;
This syntax is internally called by the CREATE GLOBAL INDEX CONCURRENTLY function to synchronize lock wait during online creation of global secondary indexes. You are advised not to use this syntax.

Parameters

IF EXISTS
Sends a notice instead of an error if no tables have identical names. The notice prompts that the table you are querying does not exist.
table_name [*] | ONLY table_name | ONLY ( table_name )
table_name is the name of the table that you need to modify.

If ONLY is specified, only the table is modified. If ONLY is not specified, the table and all subtables are modified. You can add the asterisk (*) option following the table name to specify that all subtables are scanned, which is the default operation.
constraint_name
- Specifies the name of an existing constraint to drop in the DROP CONSTRAINT operation.
- Specifies the name of a new constraint in the ADD CONSTRAINT operation.
  
  For a new constraint, constraint_name is optional in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL'). In other databases, constraint_name must be added.
index_name
Specifies the index name.
In the ADD CONSTRAINT operation:
- The index_name parameter is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
- For foreign key constraints, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
- For a unique key constraint, if both constraint_name and index_name are specified, the value of index_name is used as the index name.
USING method
Specifies the name of the index method to be used.

For details about the value range, see USING method in Parameters.
In the ADD CONSTRAINT operation:
- The USING method is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
- In MySQL-compatible mode, if USING method is not specified, the default index method is B-tree for Astore or UB-tree for Ustore.
- If the storage mode of a table is Ustore and the constraint in the SQL statement is specified as USING BTREE, the underlying layer automatically creates the constraint as USING UBTREE.
ASC | DESC
ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

In ADD CONSTRAINT, ASC|DESC is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
expression
Specifies an expression index constraint based on one or more columns of the table. It must be written in parentheses.

Expression indexes in the UNIQUE constraint are supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
storage_parameter
Specifies the name of a storage parameter.

The following options are added for online scale-out:
- append_mode (enumerated type)
  Scales out a table online or offline, or stops scaling it. You can modify certain content in the table during online scaling but cannot do so during offline scaling.
  
  To modify a table that is being scaled, append new data so that they can be recorded as incremental data.
  - on: scales out a table online. New data will be appended.
  - off: stops scaling. New data will be written in normal mode, and options for online scaling will not be displayed in pg_class.reloptions.
  - read_only: scales a table offline, during which no other operations can be performed on the table.
  - end_catchup: reports errors for the write service in the last round of data increment. The read service is executed normally.
- rel_cn_oid (OID type)
  Records the OID of tables on the current CN to generate delete_delta on the DNs.
  
  If append_mode is set to on, rel_cn_oid must be specified.
  
  The append_mode and rel_cn_oid options are used only for online scale-out tools.
- exec_step (integer)
  Records resumable transmission steps in relOptions of the temporary table.
  
  Value range: [1,4]
  
  It can be used only for data redistribution.
- create_time (long integer)
  Records the time when the temporary table is created during resumable transmission in relOptions of the temporary table.
  
  Only the data redistribution tool is supported.
- wait_clean_cbi (string type)
  Specifies whether the current global index contains the residual tuple generated during bucket migration for scale-out. After scale-out, wait_clean_cbi is set to y. After the residual tuple is cleared in the vacuum process, wait_clean_cbi is set to n.
  
  This option is used only in scaling tools.
- enable_update_distkey
  Determines whether the table supports the UPDATE operation on distribution keys. The distribution keys can be updated only when this attribute of the table is set to on and constraints are met. Otherwise, distribution keys do not support the UPDATE operation.
  
  Value range: on and off
  
  Default value: off
  The constraints on updating distribution keys are as follows:
  - Distribution columns can be updated only when enable_update_distkey is set to on.
  - The UPDATE statement cannot be pushed down to DNs for execution. The PGXC plan is directly generated and is not changed based on the values before and after the distribution key is updated.
  - Tables with row-level UPDATE TRIGGER are not supported. Otherwise, the execution fails and an error message is displayed. The row-level INSERT/DELETE TRIGGER does not take effect, and the update-statement-level TRIGGER is executed normally.
  - Concurrent update of the same row is not supported. You need to obtain a lock first. The result (returning 0 or reporting an error) of obtaining a lock on DNs depends on the setting of the GUC parameter concurrent_dml_mode. If an error is reported, the possible causes are as follows: (1) The error message "update distribute column conflict" is reported. (2) When the lock obtaining time exceeds the threshold, an error is reported, indicating that the lock times out.
  - Tables with global secondary indexes (GSIs) are not supported. Otherwise, an error is reported.
  - Only Hash distribution is supported. LIST/RANGE distribution tables are not supported. Otherwise, an error is reported.
  - MERGE INTO and UPSERT cannot be used to update distribution keys. Otherwise, an error is reported.
  - GTM_FREE is not supported. Otherwise, an error is reported.
  - UPDATE RETURNING is not supported. Otherwise, an error is reported.
  - Statements with joined tables are not supported. Otherwise, an error is reported.
  - UPDATE+LIMIT is not supported. Otherwise, an error is reported.
The following option is added for creating an index:
- parallel_workers (int type)
  Number of bgworker threads started when an index is created. For example, value 2 indicates that two bgworker threads are started to create indexes concurrently.
  
  Value range: [0,32]. The value 0 indicates that concurrent index creation is disabled.
  
  Default value: If this parameter is not set, the concurrent index creation function is disabled.
The following option is added to the replication table:
- primarynode (Boolean type)
  Default value: off
  
  When primarynode is set to on, the primary node is selected for the replication table. Generally, the primary node is the first node recorded in the nodeoids column in the pgxc_class table. When the IUD operation is performed on the replication table, the operation is delivered to the primary node first. After the result is received, the operation is delivered to other DNs.
- logical_repl_node (string type)
  Name of the DN that returns logical logs to the CN during logical decoding of a distributed replication table. For the replication table, if this parameter is not specified, the first node in the node group where the current table is located is used by default. When the RESET operation is performed on this option, logical_repl_node is reset to the first node of current table.
  
  Value range: a string
  
  Default value: For the non-replication table, this parameter is empty by default. For the replication table, this parameter is set to the name of the first node by default.
TDE options:
- enable_tde (Boolean type)
  Specifies whether to set a table as an encrypted table. This parameter applies only to row-store tables, segment-page tables, hash bucket tables, temporary tables, and unlogged tables. When setting enable_tde to on, ensure that the TDE function has been enabled using the GUC parameter enable_tde and the information for accessing the key service has been set using the GUC parameter tde_key_info. For details about how to use this parameter, see section "Transparent Data Encryption" in Feature Guide.
  
  Value range: on and off.
  
  on: Transparent data encryption is enabled.
  
  off: Transparent data encryption is disabled.
  - After the value is changed from on to off, the inserted or updated data is still encrypted when being written to the old page, and is not encrypted when being written to the new page generated after the switchover.
  - After the value is changed from off to on, the inserted or updated data is not encrypted when being written to the old page, and is automatically encrypted when being written to the new page generated after the switchover.
  That is, the encryption status on the new data page is the same as that after the encryption switchover, and the encryption status on the old data page is the same as that before the switchover. You are advised to manually perform VACUUM FULL on the table after the encryption switchover to ensure that the encryption status of all data pages is consistent.
  Default value: off
- encrypt_algo (string type)
  Specifies the encryption algorithm of the encryption table.
  
  Value range: a string. The value can be AES_128_CTR or SM4_CTR.
  
  Default value: AES_128_CTR if enable_tde is set to on; otherwise, null.
- hasuids (Boolean type)
  Default value: off
  
  If this parameter is set to on, a unique table-level ID is allocated to a tuple when the tuple is updated.
- statistic_granularity
  Records the default partition_mode when the table analyzes statistics. For details about partition_mode, see ANALYZE | ANALYSE. This parameter is invalid for non-partitioned tables.
  
  Value range: See the value range of partition_mode.
  
  Default value: AUTO
new_owner
Specifies the name of the new table owner.
new_tablespace
Specifies the new name of the tablespace to which the table belongs.
column_name, column_1_name, column_2_name
Specifies the name of a new or existing column.
data_type
Specifies the type of a new column or a new type of an existing column.
compress_mode
Specifies whether to compress a table column. The clause specifies the compression algorithm preferentially used by the column. Row-store tables do not support compression.
charset
Specifies the character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

This syntax is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
collation
Specifies the collation rule name of a column. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column. You can run the select * from pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

In a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL'), utf8mb4_bin, utf8mb4_general_ci, utf8mb4_unicode_ci, binary, gbk_chinese_ci, gbk_bin, gb18030_chinese_ci, and gb18030_bin are also supported. For details, see Table 1.
- Only the character type supports the specified character set. If the binary character set or collation is specified, the character type is converted to the corresponding binary type. If the type mapping does not exist, an error is reported. Currently, only the mapping from the TEXT type to the BLOB type is available.
- Except the binary character set and collation, only the character set that is the same as the database encoding can be specified.
- If the character set or collation of a column is not explicitly specified and the default character set or collation of the table is specified, the character set or collation of the column is inherited from the table. If the default character set or collation of a table does not exist, the character set and collation of table columns inherit the character set and collation of the current database when b_format_behavior_compat_options contains 'default_collation'.
- If the character set corresponding to the modified character set or collation is different from the character set of current column, the data in the column is converted to the specified character set for encoding.
USING expression
Specifies how to compute the new column value from the old; if omitted, the default conversion is an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from the old to new type.

USING in ALTER TYPE can specify any expression involving the old values of the row; that is, it can refer to any columns other than the one being cast. This allows general casting to be done with the ALTER TYPE syntax. Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, ALTER TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.
NOT NULL | NULL
Sets whether the column allows null values.
ENABLE
Specifies that the constraint is enabled. By default, the constraint is enabled.
integer
Specifies the constant value of a signed integer. When using PERCENT, the range of integer is from 0 to 100.
attribute_option
Specifies an attribute option.
PLAIN | EXTERNAL | EXTENDED | MAIN
Specifies a column-store mode.
- PLAIN must be used for fixed-length values (such as integers). It must be inline and uncompressed.
- MAIN is for inline, compressible data.
- EXTERNAL is for external, uncompressed data. Use of EXTERNAL will make substring operations on text and bytea values run faster, at the penalty of increased storage space.
- EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage.
CHECK ( expression )
New rows or rows to be updated must satisfy for an expression to be true. If any row produces a false result, an error is raised and the database is not modified.

A check constraint specified as a column constraint should reference only the column's values, while an expression in a table constraint can reference multiple columns.

Currently, CHECK ( expression ) does not include subqueries and cannot use variables apart from the current column.
DEFAULT default_expr
Assigns a default data value to a column.

The data type of the default expression must match the data type of the column.

The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is NULL.
ON UPDATE update_expr
The ON UPDATE clause is an attribute constraint of a column.

When an UPDATE operation is performed on a tuple in a table, if new values of updated columns are different from old values in the table, column values with this attribute but not in updated columns are automatically updated to the current timestamp. If new values of updated columns are the same as old values in the table, column values with this attribute but not in updated columns remain unchanged. If columns with this attribute are in updated columns, column values are updated according to the specified update value.
- This attribute can be specified only in MySQL 5.7 (that is, sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1').
- In terms of syntax, update_expr supports three keywords: CURRENT_TIMESTAMP, LOCALTIMESTAMP, and NOW(). You can also specify or not specify the precision of a keyword with parentheses. For example, ON UPDATE CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP(5), ON UPDATE LOCALTIMESTAMP(), and ON UPDATE LOCALTIMESTAMP(6). If the keyword does not contain parentheses or contains empty parentheses, the precision is 0. The NOW keyword cannot contain parentheses. The three types of keywords are synonyms of each other and have the same attribute effect.
- This attribute can be specified only for columns of the following types: timestamp, datetime, date, time without time zone, smalldatetime, and abstime.
- The CREATE TABLE AS syntax does not inherit the column attributes.
- The CREATE TABLE LIKE syntax can use INCLUDING UPDATE or EXCLUDING UPDATE to inherit or exclude a constraint. The LIKE syntax is inherited from the LIKE syntax of PostgreSQL. Currently, the ILM policy information of the old table cannot be copied.
- The precision specified by this attribute can be different from the precision specified by the type in the corresponding column. After the column value is updated through this attribute, the minimum precision is displayed. For example, ALTER TABLE t1 ADD col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(3);. If the UPDATE syntax triggers the attribute to take effect, three decimal places in the value of col1 are displayed after the update.
- The same column cannot be specified for this attribute and the generated column constraint at the same time.
- This attribute cannot be specified for the partition key in a partitioned table.
- In distributed scenarios, this attribute cannot be specified for distribution keys and primary keys.
AUTO_INCREMENT
Specifies an auto-increment column.

For details, see AUTO_INCREMENT.
COLUMN_ENCRYPTION_KEY = column_encryption_key
Specifies the name of the column encryption key in the ENCRYPTED WITH constraint.

Value range: a string. It must comply with the naming convention.
ENCRYPTION_TYPE = encryption_type_value
For the encryption type in the ENCRYPTED WITH constraint, the value of encryption_type_value is DETERMINISTIC or RANDOMIZED.
COMMENT [ = ] 'string'
- The COMMENT [ = ] 'string' clause is used to add comments to a table.
- The COMMENT 'string' in column_constraint indicates that comments are added to a column.
- The COMMENT 'string' in table_constraint indicates that comments are added to the indexes corresponding to the primary key and unique key.
For details, see COMMENT [ = ] 'string'.
UNIQUE [KEY] index_parameters
UNIQUE specifies that a group of one or more columns of a table can contain only unique values.

For the purpose of a unique constraint, null is not considered equal.

UNIQUE KEY can be used only when sql_compatibility is set to 'MYSQL', which has the same semantics as UNIQUE.
UNIQUE [ index_name ][ USING method ] ( { {column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters
UNIQUE specifies that a group of one or more columns of a table can contain only unique values.

column_name (length) is the prefix key. For details, see column_name ( length ).

For the purpose of a unique constraint, null is not considered equal.
- The index_name parameter is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
- For a unique key constraint, if both constraint_name and index_name are specified, the value of index_name is used as the index name.
PRIMARY KEY index_parameters
PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters

Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.
USING method
Specifies the name of the index method to be used.

For details about the value range, see the USING method in Parameters.
- The USING method is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
- In MySQL-compatible mode, if USING method is not specified, the default index method is B-tree for Astore or UB-tree for Ustore.
- For the constraint of building a global secondary index by default, the bottom layer of the global secondary index uses UB-tree storage, even if the storage mode specified by the user is B-tree.
- If the storage mode of a table is Ustore and the constraint in the SQL statement is specified as USING BTREE, the underlying layer automatically creates the constraint as USING UBTREE.
ASC | DESC
ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

ASC|DESC is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
expression
Specifies an expression index constraint based on one or more columns of the table. It must be written in parentheses.

Expression indexes in the UNIQUE constraint are supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE
Sets whether the constraint can be deferrable.
- DEFERRABLE: deferrable to the end of the transaction and checked using SET CONSTRAINTS.
- NOT DEFERRABLE: checks immediately after the execution of each command.
- INITIALLY IMMEDIATE: checks immediately after the execution of each statement.
- INITIALLY DEFERRED: checks when the transaction ends.
WITH ( {storage_parameter = value} [, ... ] )
Specifies an optional storage parameter for a table or an index.
tablespace_name
Specifies the name of the tablespace where the index locates.
COMPRESS|NOCOMPRESS
- NOCOMPRESS: If the NOCOMPRESS keyword is specified, the existing compression feature of the table will not be changed.
- COMPRESS: If the COMPRESS keyword is specified, the table compression feature will be triggered by batch tuple insertion. Row-store tables do not support compression.
new_table_name
Specifies the new table name.
new_column_name
Specifies the new name of a specific column in a table.
new_constraint_name
Specifies the new name of a table constraint.
new_schema
Specifies the new schema name.
CASCADE
Automatically drops objects that depend on the dropped column or constraint (for example, views referencing the column).
RESTRICT
Refuses to drop the column if the column is referenced by other columns or constraints. RESTRICT is the default option. If CASCADE is not specified, the value is RESTRICT. An example of the statement is as follows:
```
ALTER TABLE table_name [DROP [column] col_name [CASCADE | RESTRICT]];
table_name indicates the table name, and col_name indicates the column name.
```
schema_name
Specifies the schema name of a table.
IF NOT EXISTS
When IF NOT EXISTS is specified and columns with the same name exist, a notice is returned, indicating that the column already exists. When IF NOT EXISTS is not specified and columns with the same name exist, an error is returned.
[DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset
Changes the default character set of the table. If you specify a character set separately, the default collation of the table is set to the default collation of the specified character set.

This syntax is supported only when sql_compatibility is set to 'MYSQL'.
[DEFAULT] COLLATE [ = ] default_collation
Changes the default collation of the table. If you specify a collation separately, the default character set of the table is set to the character set corresponding to the specified collation.

This syntax is supported only when sql_compatibility is set to 'MYSQL'. For details about the collation, see Table 1.

If the character set or collation of a table is not explicitly specified and the default character set or collation of the schema is specified, the character set or collation of the table is inherited from the schema. If the default character set or collation of a schema does not exist, the character set and collation of the table inherit the character set and collation of the current database when b_format_behavior_compat_options contains 'default_collation'.

Examples of Modifying a Table

Rename a table.

gaussdb=# CREATE TABLE aa(c1 int, c2 int);
gaussdb=# ALTER TABLE IF EXISTS aa RENAME TO test_alt1;

Modify the schema of a table.

-- Create the test_schema schema.
gaussdb=# CREATE SCHEMA test_schema;

-- Change the schema of the test_alt1 table to test_schema.
gaussdb=# ALTER TABLE test_alt1 SET SCHEMA test_schema;

-- Query table information.
gaussdb=# SELECT schemaname,tablename FROM pg_tables WHERE tablename = 'test_alt1';
 schemaname  | tablename 
-------------+-----------
 test_schema | test_alt1
(1 row)

Change the owner of a table.

-- Create user test_user.
gaussdb=# CREATE USER test_user PASSWORD '********';

-- Change the owner of the test_alt1 table to test_user.
gaussdb=# ALTER TABLE IF EXISTS test_schema.test_alt1 OWNER TO test_user;

-- Query.
gaussdb=# SELECT tablename, schemaname, tableowner FROM pg_tables WHERE tablename = 'test_alt1';
 tablename | schemaname  | tableowner 
-----------+-------------+------------
 test_alt1 | test_schema | test_user
(1 row)

Modify the tablespace of a table.

-- Create the tbs_data1 tablespace.
gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';

-- Change the tablespace of the test_alt1 table to tbs_data1.
gaussdb=# ALTER TABLE test_schema.test_alt1 SET TABLESPACE tbs_data1;

-- Query.
gaussdb=# SELECT tablename, tablespace FROM pg_tables WHERE tablename = 'test_alt1';
 tablename | tablespace 
-----------+------------
 test_alt1 | tbs_data1
(1 row)

-- Delete.
gaussdb=# DROP TABLE test_schema.test_alt1; 
gaussdb=# DROP TABLESPACE tbs_data1; 
gaussdb=# DROP SCHEMA test_schema; 
gaussdb=# DROP USER test_user;

Examples of Modifying a Column

Change column names.

-- Create a table.
gaussdb=# CREATE TABLE test_alt2(c1 INT,c2 INT);

-- Change column names.
gaussdb=# ALTER TABLE test_alt2 RENAME c1 TO id;
gaussdb=# ALTER TABLE test_alt2 RENAME COLUMN c2 to areaid; 

-- Query.
gaussdb=# \d test_alt2
   Table "public.test_alt2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 areaid | integer |

Add columns.

-- Add a column to the test_alt2 table.
gaussdb=# ALTER TABLE IF EXISTS test_alt2 ADD COLUMN name VARCHAR(20);

-- Query.
gaussdb=# \d test_alt2
           Table "public.test_alt2"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | 
 areaid   | integer               | 
 name     | character varying(20) |

Add auto-increment columns.

-- Add the AUTO_INCREMENT column to the local_autoinc table. (Change the DN name by running SELECT node_name FROM pgxc_node WHERE node_type = 'D' based on the actual situation.)
gaussdb=# CREATE TABLE local_autoinc(col1 int)
DISTRIBUTE BY LIST(col1)(
    SLICE s1 VALUES (1) DATANODE datanode1,
    SLICE s2 VALUES (2) DATANODE datanode2
);
-- Distribute data to DN1.
gaussdb=# INSERT INTO local_autoinc(col1) VALUES(1);

-- Add a local auto-increment column. The value starts from 1 for each DN.
gaussdb=# ALTER TABLE local_autoinc ADD COLUMN col int AUTO_INCREMENT;
gaussdb=# SELECT col,col1 FROM local_autoinc ORDER BY 2,1;
 col | col1
-----+------
   1 |    1
(1 row)

-- Set the next auto-increment value of all DNs to 10.
gaussdb=# ALTER TABLE local_autoinc AUTO_INCREMENT = 10;

-- Distribute data to DN1. The value NULL triggers auto-increment, and the auto-increment value is 10.
gaussdb=# INSERT INTO local_autoinc(col, col1) VALUES(NULL,1);

-- Distribute data to DN2. The value 0 triggers auto-increment, and the auto-increment value is 10.
gaussdb=# INSERT INTO local_autoinc(col, col1) VALUES(0,2);
gaussdb=# SELECT col,col1 FROM local_autoinc ORDER BY 2,1;
 col | col1
-----+------
   1 |    1
  10 |    1
  10 |    2
(3 rows)

Modify the data type of a column.

-- Change the type of the name column in the test_alt2 table.
gaussdb=# ALTER TABLE test_alt2 MODIFY name VARCHAR(50);

-- Query.
gaussdb=# \d test_alt2
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 areaid | integer               | 
 name   | character varying(50) |

-- Change the type of the name column in the test_alt2 table.
gaussdb=# ALTER TABLE test_alt2 ALTER COLUMN name TYPE VARCHAR(25);

-- Query.
gaussdb=# \d test_alt2
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 areaid | integer               | 
 name   | character varying(25) |

Delete a column.

-- Delete the areaid column from test_alt2.
gaussdb=# ALTER TABLE test_alt2 DROP COLUMN areaid;

-- Query.
gaussdb=# \d test_alt2
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(25) |

Modify the column-store mode.

-- View table details.
gaussdb=# \d+ test_alt2
                              Table "public.test_alt2"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(25) |           | extended |              | 
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no

-- Change the storage mode of the name column in the test_alt2 table.
gaussdb=# ALTER TABLE test_alt2 ALTER COLUMN name SET STORAGE PLAIN;

-- Query.
gaussdb=# \d+ test_alt2
                             Table "public.test_alt2"
 Column |         Type          | Modifiers | Storage | Stats target | Description 
--------+-----------------------+-----------+---------+--------------+-------------
 id     | integer               |           | plain   |              | 
 name   | character varying(25) |           | plain   |              | 
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no

-- Delete.
gaussdb=# DROP TABLE test_alt2;

Examples of Modifying a Constraint

Add a NOT NULL constraint to a column.

-- Create a table.
gaussdb=# CREATE TABLE test_alt3(pid INT, areaid CHAR(5), name VARCHAR(20));

Add a NOT NULL constraint to pid.
gaussdb=# ALTER TABLE test_alt3 MODIFY pid NOT NULL;

-- Query.
gaussdb=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | not null
 areaid | character(5)          | 
 name   | character varying(20) |

Cancel the NOT NULL constraint on a column.

gaussdb=# ALTER TABLE test_alt3 MODIFY pid NULL;
-- Query.
gaussdb=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | 
 areaid | character(5)          | 
 name   | character varying(20) |

Modify the default value of a column.

-- Modify the default value of id in the test_alt3 table.
gaussdb=# ALTER TABLE test_alt3 ALTER COLUMN areaid SET DEFAULT '00000';

-- Query.
gaussdb=# \d test_alt3
                 Table "public.test_alt3"
 Column |         Type          |        Modifiers        
--------+-----------------------+-------------------------
 pid    | integer               | 
 areaid | character(5)          | default '00000'::bpchar
 name   | character varying(20) |

-- Delete the default value of id.
gaussdb=# ALTER TABLE test_alt3 ALTER COLUMN areaid DROP DEFAULT;

-- Query.
gaussdb=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | 
 areaid | character(5)          | 
 name   | character varying(20) |

Add a table-level constraint.

Directly add a constraint.

-- Add a primary key constraint to the table.
gaussdb=# ALTER TABLE test_alt3 ADD CONSTRAINT pk_test3_pid PRIMARY KEY (pid);

-- Query.
gaussdb=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | not null
 areaid | character(5)          | 
 name   | character varying(20) | 
Indexes:
    "pk_test3_pid" PRIMARY KEY, btree (pid) TABLESPACE pg_default

Create an index and then add constraints.

-- Create a table.
gaussdb=# CREATE TABLE test_alt4(c1 INT, c2 INT);

-- Create an index.
gaussdb=# CREATE UNIQUE INDEX pk_test4_c1 ON test_alt4(c1);

-- Associate the created index when adding a constraint.
gaussdb=# ALTER TABLE test_alt4 ADD CONSTRAINT pk_test4_c1 PRIMARY KEY USING INDEX pk_test4_c1;

-- Query.
gaussdb=# \d test_alt4
   Table "public.test_alt4"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | not null
 c2     | integer | 
Indexes:
    "pk_test4_c1" PRIMARY KEY, btree (c1) TABLESPACE pg_default

-- Delete.
gaussdb=# DROP TABLE test_alt4;

Delete a table-level constraint.

-- Delete a constraint.
gaussdb=# ALTER TABLE test_alt3 DROP CONSTRAINT IF EXISTS pk_test3_pid;

-- Query.
gaussdb=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | not null
 areaid | character(5)          | 
 name   | character varying(20) | 

-- Delete.
gaussdb=# DROP TABLE test_alt3;

-- Delete a primary key constraint. (This syntax is supported only in MySQL-compatible mode.)
gaussdb=# CREATE TABLE test_drop_primary_key(c1 INT PRIMARY KEY);
gaussdb=# \d test_drop_primary_key
Table "public.test_drop_primary_key"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | not null
Indexes:
    "test_drop_primary_key_pkey" PRIMARY KEY, btree (c1) TABLESPACE pg_default

gaussdb=# ALTER TABLE test_drop_primary_key DROP PRIMARY KEY;
gaussdb=# \d test_drop_primary_key
Table "public.test_drop_primary_key"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | not null

Helpful Links

CREATE TABLE and DROP TABLE

Parent topic: A

Previous topic: ALTER SYSTEM KILL SESSION

Next topic: ALTER TABLE PARTITION

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot