ALTER TABLE

功能描述

修改表，包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关。

只有表的所有者或者被授予了表ALTER权限的用户有权限执行ALTER TABLE命令，系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式，当前用户必须是该表的所有者或者系统管理员，且该用户是新的所有角色的直接或间接成员。

注意事项

ALTER TABLE语法格式

1
2

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

action表示支持以下修改操作：

• 修改表已存在字段的数据类型，可同时修改空约束，default值和注释信息，只允许相同大类的类型转换（数值之间，字符串之间，时间之间等）。

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

• 重命名表。对名称的修改不会影响所存储的数据；支持新表名前带有原表的schema名，不支持同时修改schema名。

  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;

• 重命名表中指定的列。

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

• 重命名表的约束。

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

• 设置表的所属模式。

  1 2	ALTER TABLE [ IF EXISTS ] table_name SET SCHEMA new_schema;

  

  • SET SCHEMA操作不支持修改为系统内部模式，当前仅支持用户模式之间的修改。
  • 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列，需要将序列删除，重建，或者取消拥有关系， 才能将表schema更改成功。
  • 要修改一个表的模式，用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表，用户必须同时又是父表的所有者。要修改所有者，用户还必须是新的所有角色的直接或间接成员，并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能做出了重建和删除表之外的事情。不过，系统管理员可以以任何方式修改任意表的所有权限。
• 添加多个列。

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

• 更新多个列。

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

• 删除指定表上的索引。

  1	ALTER TABLE table_name DROP INDEX index_name;

• 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如，可以在一个命令里增加几个字段或修改几个字段的类型。对于大表，此种操作带来的效率提升更明显，原因在于只需要对该大表做一次处理。
• 增加一个CHECK或NOT NULL约束将会扫描该表，以保证现有的行符合约束要求。
• 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说，这个操作可能会花很长时间，并且它还临时需要两倍的磁盘空间。

表操作相关的action子句

 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 ]

  给表增加一个新的约束。与NOT VALID选项组合时，这种约束仅在对外键和CHECK类约束有效。 如果约束条件增加了NOT VALID选项后，表中已有记录是否满足初始约束检查会被跳过。

• ADD table_constraint_using_index

  根据已有唯一索引为表增加主键约束或唯一约束。

• VALIDATE CONSTRAINT constraint_name

  验证一个外键或是一个使用NOT VALID选项创建的检查类约束，通过扫描全表来保证所有记录都符合约束条件。如果约束已标记为有效时，什么操作也不会发生。

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

  删除一个表上的约束。

• CLUSTER ON index_name

  为将来的CLUSTER操作选择默认索引。实际上并没有重新盘簇化处理该表。

• SET WITHOUT CLUSTER

  从表中删除最新使用的CLUSTER索引。这样会影响将来那些没有声明索引的集群操作。

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

  修改表的一个或多个存储参数。

• RESET ( storage_parameter [, ... ] )

  重置表的一个或多个存储参数。与SET一样，根据参数的不同可能需要重写表才能获得想要的效果。

• OWNER TO new_owner

  将表、序列、视图的属主改变成指定的用户。

• SET {COMPRESS|NOCOMPRESS}

  修改表的压缩特性。表压缩特性的改变只会影响后续批量插入的数据的存储方式，对已有数据的存储毫无影响。也就是说，表压缩特性的修改会导致该表中同时存在着已压缩和未压缩的数据。

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

  修改表的分布方式，在修改表分布信息的同时会将表数据在物理上按新分布方式重新分布，修改完成后建议对被修改表执行ANALYZE，以便收集全新的统计信息。

  

  • 本操作属于重大变更操作，涉及表分布信息的修改以及数据的物理重分布，修改过程中会阻塞业务，修改完成后原有业务的执行计划会发生变化，请按照正规变更流程进行。
  • 本操作属于资源密集操作，针对大表的分布方式修改，建议在计算和存储资源充裕情况下进行，保证整个集群和原表所在表空间有足够的剩余空间能存储一张与原表同等大小且按照新分布方式进行分布的表。
• TO { GROUP groupname | NODE ( nodename [, ... ] ) }

  此语法仅在扩展模式（GUC参数enable_cluster_resize为on时）下可用。该模式谨慎打开，主要供内部扩容工具使用，一般用户不应使用该模式。

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

  此语法主要供内部扩容工具使用，一般用户不建议使用。

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

  此语法主要供内部缩容工具使用，一般用户不建议使用。

• DISABLE TRIGGER [ trigger_name | ALL | USER ]

  禁用trigger_name所表示的单个触发器，或禁用所有触发器，或仅禁用用户触发器（此选项不包括内部生成的约束触发器，例如，可延迟唯一性和排除约束的约束触发器）。

  

  应谨慎使用此功能，因为如果不执行触发器，则无法保证原先期望的约束的完整性。

• ENABLE TRIGGER [ trigger_name | ALL | USER ]

  启用trigger_name所表示的单个触发器，或启用所有触发器，或仅启用用户触发器。

• ENABLE REPLICA TRIGGER trigger_name

  触发器触发机制受配置变量session_replication_role的影响，当复制角色为“origin”（默认值）或“local”时，将触发简单启用的触发器。

  配置为ENABLE REPLICA的触发器仅在会话处于“replica”模式时触发。

• ENABLE ALWAYS TRIGGER trigger_name

  无论当前复制模式如何，配置为ENABLE ALWAYS的触发器都将触发。

• DISABLE/ENABLE ROW LEVEL SECURITY

  开启或关闭表的行访问控制开关。

  当开启行访问控制开关时，如果未在该数据表定义相关行访问控制策略，数据表的行级访问将不受影响；如果关闭表的行访问控制开关，即使定义了行访问控制策略，数据表的行访问也不受影响。详细信息参见CREATE ROW LEVEL SECURITY POLICY章节。

  

  不支持对HDFS表、外表、临时表开启行访问控制开关。

• NO FORCE/FORCE ROW LEVEL SECURITY

  强制开启或关闭表的行访问控制开关。

  默认情况，表所有者不受行访问控制特性影响，但当强制开启表的行访问控制开关时，表的所有者（不包含系统管理员用户）会受影响。系统管理员可以绕过所有的行访问控制策略，不受影响。

• REFRESH STORAGE

  根据OBS冷热表storage_policy所定义的规则，将符合条件的本地热分区切换为存储在OBS上的冷分区。

  例如创建OBS冷热表时，设置storage_policy为 'LMT:10'，则在执行该操作时可将10日前无修改的分区切为冷存储，存至OBS中。

列相关的操作column_clause子句

 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 }

其他参数的选项

• 列约束column_constraint为：

  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为：

  1	[ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS ]

• 根据已有唯一索引为表增加主键约束或唯一约束table_constraint_using_index为：

  1 2 3

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

• 增加外键约束REFERENCES为：

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

• 表约束table_constraint为：

  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为：

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

参数说明

唯一索引中索引列NULL值的处理方式的说明如下：

[ NULLS [ NOT ] DISTINCT | NULLS IGNORE ]字段用来指定Unique唯一索引中索引列NULL值的处理方式。

在对插入的新数据和表中原始数据进行列的等值比较时，对于NULL值有以下三种处理方式：

• NULLS DISTINCT：NULL值互不相等，即NULL值可重复插入。
• NULLS NOT DISTINCT：NULL值相等。若索引列全为NULL，则NULL值不可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。
• NULLS IGNORE：在等值比较时跳过NULL值。若索引列全为NULL，则NULL值可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。

表操作示例

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

创建示例列存表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);

• 修改表的模式。

  设置表customer的所属模式为myschema。

  1	ALTER TABLE customer SET SCHEMA myschema;

• 根据已有唯一索引为表增加主键约束或唯一约束。

  为表customer创建唯一索引customer_constraint1，然后根据已有唯一索引增加主键约束，并对前面创建的索引进行重命名。

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

• 重命名表约束。

  1	ALTER TABLE customer RENAME CONSTRAINT customer_constraint2 TO customer_constraint;

• 删除表约束。

  1	ALTER TABLE customer DROP CONSTRAINT customer_constraint;

• 给表新增主键约束。

  1	ALTER TABLE CUSTOMER ADD PRIMARY KEY(C_CUSTKEY);

• 向列存表customer_address中添加局部聚簇列。

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

• 删除列存表customer_address中的局部聚簇列。

  1	ALTER TABLE customer_address DROP CONSTRAINT customer_address_cluster;

• 修改列存表的存储格式。

  1	ALTER TABLE customer_address SET (COLVERSION = 1.0);

• 修改表的分布方式。

  1	ALTER TABLE customer_address DISTRIBUTE BY REPLICATION;

• 重命名表。

  1	ALTER TABLE CUSTOMER RENAME TO CUSTOMER_t;

• 修改表缓存策略（仅存算分离3.0版本集群支持）。

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

• 关闭表自动删除分区功能。创建表的示例可参考示例：创建指定分区的自动分区管理分区表。

  1	ALTER TABLE CPU RESET (ttl);

索引操作示例

• 给表增加一个索引。

  1	ALTER TABLE customer ADD INDEX customer_index(C_CUSTKEY);

• 删除表索引。

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

• 给表中某一列添加索引。

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

冷热表操作示例

• 单表冷热切换。

  1	ALTER TABLE cold_hot_table REFRESH STORAGE;

• 列存分区表修改为冷热表。

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

列操作示例

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

• 增加列。
  向表warehouse_t中增加单列。

  1	ALTER TABLE warehouse_t ADD W_GOODS_CATEGORY int;

  向表warehouse_t中增加多列。

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

• 删除列。
  从表warehouse_t中删除一列。

  1	ALTER TABLE warehouse_t DROP COLUMN W_STATE;

• 重命名列。
  表warehouse_t的列名W_ZIP修改为new_W_ZIP。

  1	ALTER TABLE warehouse_t RENAME W_ZIP TO new_W_ZIP;

• 变更列名同时修改列字段信息。
  表warehouse_t的列名W_GOODS_CATEGORY修改为W_GOODS_CATEGORY2，同时修改W_GOODS_CATEGORY2字段数据类型为DECIMAL，字段约束为非空。

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

• 变更列的注释信息（COMMENT）。

  1	ALTER TABLE warehouse_t MODIFY W_GOODS_CATEGORY2 COMMENT 'W_GOODS_CATEGORY';

• 变更列的数据类型。
  修改表字段warehouse_t的数据类型，并将字段约束设置为非空。

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

• 指定列添加非空约束（not null）。

  1	ALTER TABLE warehouse_t ALTER COLUMN W_COUNTY SET NOT NULL;

• 为表warehouse_t增加主键。

  1	ALTER TABLE warehouse_t ADD PRIMARY KEY(w_warehouse_name);

• 修改表中指定列的on update表达式。
  为warehouse_t表增加一个带有on update的timestamp列，在表执行UPDATE操作时自动更新该列的时间戳。

  1	ALTER TABLE warehouse_t ADD COLUMN C_TIME timestamp on update current_timestamp;

  将warehouse_t表中带有on update的timestamp列删除。

  1	ALTER TABLE warehouse_t MODIFY COLUMN C_TIME timestamp on update NULL;

相关链接

CREATE TABLE，12.101-RENAME TABLE，DROP TABLE

常见问题

如何调整DWS分布列？

数据类型转换出现报错：invalid input syntax for xxx

向表中插入数据报错：duplicate key value violates unique constraint "%s