ALTER TABLE

Function

ALTER TABLE is used to modify tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level access control, and adding or updating multiple columns.

Precautions

• Only the owner of a table, a user granted with the ALTER permission for the table, or a system administrator has the permission to run the ALTER TABLE statement. To change the owner or schema of a table, you need to have ownership or system administrator privileges for the table and be a direct or indirect member of the new role.
• 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.
• Column-store tables support PARTIAL CLUSTER KEY but do not support table-level foreign key constraints. In 8.1.1 or later, column-store tables support the PRIMARY KEY constraint and table-level UNIQUE constraint in table creation.
• A system column cannot be designated as a primary key in a row-store REPLICATION distributed table.
• In a column-store table, you can perform ADD COLUMN, ALTER TYPE, SET STATISTICS, DROP COLUMN operations. The types of new and modified columns should be the data types supported by column storage (for details, see Data Types). The USING option of ALTER TYPE only supports constant expression and expression involved in the column.
• The column constraints supported by column-store tables include NULL, NOT NULL, and DEFAULT constant values. Only the DEFAULT value can be modified (SET DEFAULT and DROP DEFAULT), and only the NOT NULL constraint can be deleted.
• The NOT NULL constraint and PRIMARY KEY constraint can be added to existing column-store tables using ALTER. This constraint is supported by version 8.2.0 or later clusters.
• When you modify the COLVERSION or enable_delta parameter of a column-store table, other ALTER operations cannot be performed.

ALTER TABLE syntax format

1
2

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];

• Changes the data type of an existing column in the table. Only the type conversion of the same category (between values, strings, and time) is allowed.

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

• Rename the table. Changing the table name does not affect the stored data. The new table name can be prefixed with the schema name of the original table, but the schema name cannot be changed at the same time.

  1 2 3 4

  ALTER TABLE [ IF EXISTS ] table_name RENAME TO new_table_name; ALTER TABLE [ IF EXISTS ] table_name RENAME TO schema.new_table_name;

• Rename the specified column in the table.

  1 2	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.

  1 2	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 CREATE privilege 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 CREATE permission on the table's schema. These restrictions enforce that altering the owner does not do anything you could not do by dropping and recreating the table. However, a system administrator can alter 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 command. This is useful with large tables, since only one pass over the table need be made.
  • Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.
  • Adding a column with a non-null default or changing the type of an existing column will require the entire table to be rewritten. Table rebuilding may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
• Add columns.

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

• Update columns.

  1 2	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 } [, ...] );

• Delete an index from a specified table.

  1	ALTER TABLE table_name DROP INDEX index_name;

Clauses of action Related to Table Operations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

column_clause
    | ADD table_constraint [ NOT VALID ]
    | ADD table_constraint_using_index
    | VALIDATE CONSTRAINT constraint_name
    | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    | 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}
    | DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } }
    | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
    | ADD NODE ( nodename [, ... ] )
    | DELETE NODE ( nodename [, ... ] )
    | DISABLE TRIGGER [ trigger_name | ALL | USER ]
    | ENABLE TRIGGER [ trigger_name | ALL | USER ]
    | ENABLE REPLICA TRIGGER trigger_name
    | ENABLE ALWAYS TRIGGER trigger_name
    | DISABLE ROW LEVEL SECURITY
    | ENABLE ROW LEVEL SECURITY
    | FORCE ROW LEVEL SECURITY
    | NO FORCE ROW LEVEL SECURITY
    | REFRESH STORAGE

• ADD table_constraint [ NOT VALID ]

  Adds a new table constraint. When used with the NOT VALID option, this constraint is valid only for foreign keys and CHECK constraints. If the NOT VALID option is added to the constraint, the check on whether the existing records in the table meet the initial constraint is skipped.

• ADD table_constraint_using_index

  Adds primary key constraint or unique constraint based on the unique index.

• VALIDATE CONSTRAINT constraint_name

  Validates a foreign key or check constraint that was previously created as NOT VALID, by scanning the table to ensure there are no rows for which the constraint is not satisfied. Nothing happens if the constraint is already marked valid.

• DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

  Drops a table constraint.

• CLUSTER ON index_name

  Selects the default index for future CLUSTER operations. It does not actually re-cluster the table.

• SET WITHOUT CLUSTER

  Removes the most recently used CLUSTER index specification from the table. This operation affects future cluster operations that do not specify an index.

• SET ( {storage_parameter = value} [, ... ] )

  Changes one or more storage parameters for the table.

• 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 the table, sequence, or view to the specified user.

• 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.

• DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } }

  Changing a table's distribution mode will physically redistribute the table data based on the new distribution mode. After the distribution mode is changed, you are advised to manually run the ANALYZE statement to collect new statistics about the table.

  

  • This operation is a major change operation, involving table distribution information modification and physical data redistribution. During the modification, services are blocked. After the modification, the original execution plan of services will change. Perform this operation according to the standard change process.
  • This operation is a resource-intensive operation. If you need to modify the distribution mode of large tables, perform the operation when the computing and storage resources are sufficient. Ensure that the remaining space of the entire cluster and the tablespace where the original table is located is sufficient to store a table that has the same size as the original table and is distributed in the new distribution mode.
• TO { GROUP groupname | NODE ( nodename [, ... ] ) }

  The syntax is only available in extended mode (when GUC parameter enable_cluster_resize is on). Exercise caution when enabling the mode. It is used for tools like internal dilatation tools. Common users should not use the mode.

• ADD NODE ( nodename [, ... ] )

  It is only available for tools like internal dilatation. General users should not use the mode.

• DELETE NODE ( nodename [, ... ] )

  It is only available for internal scale-in tools. Common users should not use the syntax.

• 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 fired only when the session is in replica mode.

• ENABLE ALWAYS TRIGGER trigger_name

  Determines that all triggers are fired regardless of the current replication mode.

• DISABLE/ENABLE ROW LEVEL SECURITY

  Enables or disables row-level access control for a table.

  If row-level access control is enabled for a data table but no row-level access control policy is defined, the row-level access to the data table is not affected. If row-level access control for a table is disabled, the row-level access to the table is not affected even if a row-level access control policy has been defined. For details, see CREATE ROW LEVEL SECURITY POLICY.

• NO FORCE/FORCE ROW LEVEL SECURITY

  Forcibly enables or disables row-level access control for a table.

  By default, the table owner is not affected by the row-level access control feature. However, if row-level access control is forcibly enabled, the table owner (excluding system administrators) will be affected. System administrators are not affected by any row-level access control policies.

• REFRESH STORAGE

  Changes the local hot partitions that meet the criteria specified in the storage_policy parameter of an OBS multi-temperature table to the cold partitions stored in the OBS.

  For example, if storage_policy is set to 'LMT:10' for an OBS hot or cold table when it is created, the partitions that are not updated within the last 10 days are switched to cold partitions in the OBS.

Clauses of column_clause Related to Column Operations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]    
    | MODIFY [ COLUMN ] column_name data_type
    | MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
    | MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NULL    
    | MODIFY [ COLUMN ] column_name DEFAULT default_expr
    | MODIFY [ COLUMN ] column_name ON UPDATE on_update_expr
    | MODIFY [ COLUMN ] column_name COMMENT comment_text
    | 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 [, ...] )) 
    | ADD { INDEX | UNIQUE [ INDEX ] } [ index_name ] ( { { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] } [, ...] ) [ USING method ] [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] [ COMMENT 'text' ] LOCAL [ ( { PARTITION index_partition_name } [, ...] ) ] [ WITH ( { storage_parameter = value } [, ...] ) ]
    | ADD { INDEX | UNIQUE [ INDEX ] } [ index_name ] ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ USING method ] [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] [ COMMENT 'text' ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ WHERE predicate ]
    | DROP { INDEX | KEY } index_name
    | CHANGE [ COLUMN ] old_column_name new_column_name data_type [ [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] |
        [ CONSTRAINT constraint_name ] NULL | DEFAULT default_expr | COMMENT 'text' ]
    | 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 }

Options of Other Parameters

• column_constraint is as follows:

  1 2 3 4 5 6 7 8

  [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] index_parameters | PRIMARY KEY index_parameters } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

• compress_mode of a column is as follows:

  1	[ 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:

  1 2 3

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

• Add foreign key constraint REFERENCES.

  1 2	[ CONSTRAINT constraint_name ] FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] }

• table_constraint is as follows:

  1 2 3 4 5 6

  [ CONSTRAINT constraint_name ] { CHECK ( expression ) | UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

• index_parameters is as follows:

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

Parameter Description

The NULL value of the index column in the unique index is processed as follows:

Use the [NULLS [NOT] DISTINCT | NULLS IGNORE] field to set how null values are handled in the unique index column.

When the inserted data is compared with the original data in the table, the NULL value can be processed in any of the following ways:

• NULLS DISTINCT: NULL values are unequal and can be inserted repeatedly.
• NULLS NOT DISTINCT: NULL values are equal. If all index columns are NULL, NULL values cannot be inserted repeatedly. If some index columns are NULL, data can be inserted only when non-null values are different.
• NULLS IGNORE: NULL values are skipped during the equivalent comparison. If all index columns are NULL, NULL values can be inserted repeatedly. If some index columns are NULL, data can be inserted only when non-null values are different.

Table Operation Examples

Create the sample table customer.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DROP TABLE IF EXISTS customer;
CREATE TABLE customer
(    
    C_CUSTKEY     BIGINT       , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT          , 
    C_PHONE       CHAR(15)     , 
    C_ACCTBAL     DECIMAL(15,2)
)
DISTRIBUTE BY HASH(C_CUSTKEY);

Adds primary key constraint or unique constraint based on the unique index.

Create a unique index customer_constraint1 for the customer table, add a primary key constraint based on the existing unique index, and rename the index.

1
2

CREATE UNIQUE INDEX customer_constraint1 ON customer(C_CUSTKEY);
ALTER TABLE customer ADD CONSTRAINT customer_constraint2 PRIMARY KEY USING INDEX customer_constraint1;

Rename a table constraint.

1

ALTER TABLE customer RENAME CONSTRAINT customer_constraint2 TO customer_constraint;

Delete a table constraint.

1

ALTER TABLE customer DROP CONSTRAINT customer_constraint;

Add a table index.

1

ALTER TABLE customer ADD INDEX customer_index(C_CUSTKEY);

Delete a table index.

1
2

ALTER TABLE customer DROP INDEX customer_index;
ALTER TABLE customer DROP KEY customer_index;

Adds an index to a column in a table.

1

ALTER TABLE CUSTOMER ADD c_address_id varchar(20) CONSTRAINT ca_address_index CHECK (c_address_id > 0);

Adds a primary key constraint to a table.

1

ALTER TABLE CUSTOMER ADD PRIMARY KEY(C_CUSTKEY);

Rename the table.

1

ALTER TABLE CUSTOMER RENAME TO CUSTOMER_t;

Create a column-store table customer_address.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DROP TABLE IF EXISTS customer_address;
CREATE TABLE customer_address
(
    ca_address_sk       INTEGER                  NOT NULL   ,
    ca_address_id       CHARACTER(16)            NOT NULL   ,
    ca_street_number    CHARACTER(10)                       ,
    ca_street_name      CHARACTER varying(60)               ,
    ca_street_type      CHARACTER(15)                       ,
    ca_suite_number     CHARACTER(10)                    
)
WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH,COLVERSION=2.0)
DISTRIBUTE BY HASH (ca_address_sk);

Add a partial cluster column to the column-store table customer_address.

1

ALTER TABLE customer_address ADD CONSTRAINT customer_address_cluster PARTIAL CLUSTER KEY(ca_address_sk);

Delete the partial cluster column from the column-store table customer_address.

1

ALTER TABLE customer_address DROP CONSTRAINT customer_address_cluster;

Modify the storage format of a column-store table.

1

ALTER TABLE customer_address SET (COLVERSION = 1.0);

Change the distribution mode of a table:

1

ALTER TABLE customer_address DISTRIBUTE BY REPLICATION;

Change the table schema.

1

ALTER TABLE customer_address SET SCHEMA tpcds;

Do a cold or hot switchover for a single table.

1

ALTER TABLE cold_hot_table REFRESH STORAGE;

Change a column-store partitioned table to a hot and cold table.

1
2
3
4
5
6
7

CREATE table test_1(id int,d_time date)
WITH(ORIENTATION=COLUMN)
DISTRIBUTE BY HASH (id)
PARTITION BY RANGE (d_time)
(PARTITION p1 START('2022-01-01') END('2022-01-31') EVERY(interval '1 day'))

ALTER TABLE test_1 SET (storage_policy = 'LMT:100');

Modify the table cache policy (supported only in storage-compute decoupled 3.0 clusters).

ALTER TABLE orders SET (cache_policy = 'NONE');

Column Operation Examples

Create the sample table warehouse_t.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DROP TABLE IF EXISTS warehouse_t;
CREATE TABLE warehouse_t
(
    W_WAREHOUSE_SK            INTEGER                NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)               NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)   UNIQUE DEFERRABLE,
    W_WAREHOUSE_SQ_FT         INTEGER                        ,
    W_COUNTY                  VARCHAR(30)                    ,
    W_STATE                   CHAR(2)            DEFAULT 'GA',
    W_ZIP                     CHAR(10)                       
);

Add a new column to the warehouse_t table.

1

ALTER TABLE warehouse_t ADD W_GOODS_CATEGORY int;

Modify the column name and column field information in the table.

1

ALTER TABLE warehouse_t CHANGE W_GOODS_CATEGORY W_GOODS_CATEGORY2 DECIMAL NOT NULL COMMENT 'W_GOODS_CATEGORY';

Add a primary key to the warehouse_t table.

1

ALTER TABLE warehouse_t ADD PRIMARY KEY(w_warehouse_name);

Rename a column.

1

ALTER TABLE warehouse_t RENAME W_ZIP TO new_W_ZIP;

Add columns to a table.

1

ALTER TABLE warehouse_t ADD (W_COMMENT VARCHAR(117) NOT NULL, W_COUNT int);

Change the data type of the table column and set the column constraint to non-null.

1

ALTER TABLE warehouse_t MODIFY W_WAREHOUSE_SQ_FT varchar(20) NOT NULL;

Add the NOT NULL constraint to a specified column of a table.

1

ALTER TABLE warehouse_t ALTER COLUMN W_COUNTY SET NOT NULL;

Delete a column from a table.

1

ALTER TABLE warehouse_t DROP COLUMN W_STATE;

Add a timestamp column with on update to the warehouse_t table.

1

ALTER TABLE warehouse_t ADD COLUMN C_TIME timestamp on update current_timestamp;

Delete the timestamp column with the ON UPDATE expression from the warehouse_t table.

1

ALTER TABLE warehouse_t MODIFY COLUMN C_TIME timestamp on update NULL;

Helpful Links

CREATE TABLE, 12.101-RENAME TABLE, and DROP TABLE