文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(8.3.0.x)/ DDL语法/ ALTER TABLE
更新时间：2024-10-25 GMT+08:00
查看PDF
分享

ALTER TABLE

功能描述

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

注意事项

  • 只有表的所有者或者被授予了表ALTER权限的用户有权限执行ALTER TABLE命令，系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式，当前用户必须是该表的所有者或者系统管理员，且该用户是新的所有角色的直接或间接成员。
  • 不支持修改存储参数ORIENTATION。
  • SET SCHEMA操作不支持修改为系统内部模式，当前仅支持用户模式之间的修改。
  • 列存表支持PARTIAL CLUSTER KEY，不支持外键表级约束。列存表自8.1.1集群版本开始支持主键和唯一表级约束。
  • 行存REPLICATION分布表不支持将系统列设置为主键。
  • 列存表支持添加字段ADD COLUMN，修改字段的数据类型ALTER TYPE，设置单个字段的收集目标SET STATISTICS，支持更改表名字，支持删除字段DROP COLUMN；对于添加的字段和修改的字段类型要求是列存支持的数据类型；ALTER TYPE的USING选项只支持常量表达式和涉及本字段的表达式，暂不支持涉及其他字段的表达式。
  • 列存表支持的字段约束包括NULL、NOT NULL和DEFAULT常量值；对字段约束的修改，当前支持对DEFAULT值的修改（SET DEFAULT）、删除（DROP DEFAULT）和NOT NULL约束的删除；
  • 支持对列存表添加非空约束NOT NULL以及主键约束。该约束仅8.2.0及以上集群版本支持。
  • 修改列存表存储参数COLVERSION或者enable_delta时，不能与其他ALTER操作同时进行。
  • 不支持增加自增列，或者增加DEFAULT值中包含nextval()表达式的列。
  • 不支持对HDFS表、外表、临时表开启行访问控制开关。
  • 通过约束名删除PRIMARY KEY约束时，不会删除NOT NULL约束。如有需要，可手动删除NOT NULL约束。
  • OBS冷热表不支持ALTER RESET的cold_tablespace，storage_policy参数，不支持修改COLVERSION为1.0。
  • 可将原列存COLVERSION为2.0表ALTER为OBS冷热表，需同时增加cold_tablespace和storage_policy参数。
  • ALTER TABLE支持针对RELOPTIONS的storage_policy的选项更改。冷热切换策略发生更改后，不改变现有冷数据的冷热属性，只改变下一次执行冷热切换的规则，下一次执行冷热切换命令时将按新规则进行冷热切换。
  • 对表执行以下ALTER TABLE操作时会触发表重建（表重建过程中会先把数据转储到一个新的数据文件中，重建完成之后会删除原始文件），当表比较大时，重建会消耗较多的磁盘空间。当磁盘空间不足时，要谨慎对待大表ALTER TABLE操作，防止触发集群只读。
    • 修改列数据类型。
    • 行存表增加列（包括oid列）。
    • 列存表修改COLVERSION。
    • 列存表增加列指定DEFAULT常量值，DEFAULT值中包含volatile函数以及DEFAULT值非NULL且不属于特定数据类型中的任何一个。
  • 避免在业务高峰期执行ALTER TABLE/ALTER TABLE PARTITION（增删改查、DROP PARTITION）、TRUNCATE操作，避免有长SQL阻塞AlTER、TRUNCATE操作或SQL业务被ALTER、TRUNCATE阻塞。
  • 更多开发设计规范参见总体开发设计规范。

语法格式

  • 修改表的定义。 
    1
2
    		 
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
    其中具体表操作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 ]

      给表增加一个新的约束。

    • 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章节。

    • 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 }
      • ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]

        向表中增加一个新的字段。用ADD COLUMN增加一个字段，所有表中现有行都初始化为该字段的缺省值（如果没有声明DEFAULT子句，值为NULL）。

      • ADD ( { column_name data_type [ compress_mode ] } [, ...] )

        向表中增加多列。

      • MODIFY [ COLUMN ] column_name data_type

        修改表已存在字段的数据类型。需注意：不支持修改分布列数据类型。

      • MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]

        为表的某列添加not null约束，列存表暂不支持。

      • MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NULL

        为表的某列移除not null约束。

      • MODIFY [ COLUMN ] column_name DEFAULT default_expr

        修改表的default值。

      • MODIFY [ COLUMN ] column_name ON UPDATE on_update_expr

        修改表中指定列的on update表达式，该列必须为timestamp类型或者timestamptz类型，当on_update_expr为NULL值时，则为删除ON UPDATE子句。

      • MODIFY [ COLUMN ] column_name COMMENT comment_text

        修改表的注释信息。

      • DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

        从表中删除一个字段，和这个字段相关的索引和表约束也会被自动删除。如果任何表之外的对象依赖于这个字段，必须声明CASCADE ，比如外键参考、视图等。

        DROP COLUMN命令并不是物理上把字段删除，而只是简单地把它标记为对SQL操作不可见。随后对该表的插入和更新将在该字段存储一个NULL。因此，删除一个字段是很快的，但是它不会立即释放表在磁盘上的空间，因为被删除了的字段占据的空间还没有回收。这些空间将在执行VACUUM时而得到回收。

      • ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

        改变表字段的数据类型，只允许相同大类的类型转换（数值之间，字符串之间，时间之间等）。该字段涉及的索引和简单的表约束将被自动地转换为使用新的字段类型，方法是重新分析最初提供的表达式。

        ALTER TYPE要求重写整个表的特性有时候是一个优点，因为重写的过程消除了表中没用的空间。比如，要想立刻回收被一个已经删除的字段占据的空间，最快的方法是

        1
        		 
        ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

        这里的anycol是任何在表中还存在的字段，而anytype是和该字段的原类型一样的类型。这样的结果是在表上没有任何可见的语意的变化，但是这个命令强制重写，这样就删除了不再使用的数据。

      • ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }

        为一个字段设置或者删除缺省值。请注意缺省值只应用于随后的INSERT命令，它们不会修改表中已经存在的行。也可以为视图创建缺省，这个时候它们是在视图的ON INSERT规则应用之前插入到INSERT句中的。

      • ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

        修改一个字段是否允许NULL值或者拒绝NULL值。如果表在字段中包含非NULL，则只能使用SET NOT NULL。

      • ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer

        为随后的ANALYZE操作设置针对每个字段的统计收集目标。目标的范围可以在0到10000之内设置。设置为-1时表示重新恢复到使用系统缺省的统计目标。

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

        为表的分区表创建索引，具体参数可参考CREATE INDEX。

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

        在表上创建索引，具体参数可参考CREATE INDEX。

      • 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' ]

        修改表中列信息，可将旧列名修改成新列名，以及修改列字段信息。

      • {ADD | DELETE} STATISTICS ((column_1_name, column_2_name [, ...]))

        用于添加和删除多列统计信息声明（不实际进行多列统计信息收集），以便在后续进行全表或全库analyze时进行多列统计信息收集。每组多列统计信息最多支持32列。不支持添加/删除多列统计信息声明的表：系统表、外表。

      • ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )

        ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

        设置/重置属性选项。

        属性选项定义的参数有：n_distinct、n_distinct_inherited和cstore_cu_sample_ratio。n_distinct 设置并固定表的distinct值统计信息，n_distinct_inherited 设置并固定继承表的distinct值统计信息，cstore_cu_sample_ratio 设置对cstore列存表进行analyze时所选CU的比例。目前，禁止SET/RESET n_distinct_inherited参数。
        • n_distinct

          手动设置该列的distinct值统计信息。

          取值范围：-1.0 ~ INT_MAX

          默认值：0，表示不设置。

        • n_distinct_inherited

          手动设置继承表的该列的distinct值统计信息。

          取值范围：-1.0 ~ INT_MAX

          默认值：0，表示不设置。

        • cstore_cu_sample_ratio

          设置列存表执行analyze，计算需要采样的CU个数时，需要扩大的倍数。

          取值范围：1.0 ~ 10000.0

          默认值：1.0

      • ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

        为一个字段设置存储模式。这个设置控制这个字段是内联保存还是保存在一个附属的表里，以及数据是否要压缩。仅支持对行存表的设置；对列存表没有意义，执行时报错。SET STORAGE本身并不改变表上的任何东西，只是设置将来的表操作时，建议使用的策略。

      • 其中列约束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 ]
    • 其中表约束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 ]
  • 修改表已存在字段的数据类型，可同时修改空约束，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 { 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;
    • 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列，需要将序列删除，重建，或者取消拥有关系， 才能将表schema更改成功。
    • 要修改一个表的模式，用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表，用户必须同时又是父表的所有者。要修改所有者，用户还必须是新的所有角色的直接或间接成员，并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能做出了重建和删除表之外的事情。不过，系统管理员可以以任何方式修改任意表的所有权限。
    • 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如，可以在一个命令里增加几个字段或修改几个字段的类型。对于大表，此种操作带来的效率提升更明显，原因在于只需要对该大表做一次处理。
    • 增加一个CHECK或NOT NULL约束将会扫描该表，以保证现有的行符合约束要求。
    • 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说，这个操作可能会花很长时间，并且它还临时需要两倍的磁盘空间。
  • 添加多个列。 
    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 } [, ...] );

参数说明

  • IF EXISTS

    如果不存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表不存在。

  • table_name [*] | ONLY table_name | ONLY ( table_name )

    table_name是需要修改的表名。

    若声明了ONLY选项，则只有那个表被更改。若未声明ONLY，该表及其所有子表都将会被更改。另外，可以在表名称后面显示地增加*选项来指定包括子表，即表示所有后代表都被扫描，这是默认行为。

  • constraint_name

    约束的名字。约束名长度不超过63个字符。

  • index_name

    索引名称。

  • storage_parameter

    表的存储参数的名字。

    分区管理新增的两个选项：

    • PERIOD（interval类型）

      设置分区管理中自动创建分区的周期。

      PERIOD的范围要求以及开启该功能的约束请参考▪PERIOD。

      • 在建表时，如果没有设置该参数，可以通过set的方式添加该参数，并开启自动创建分区功能；如果之前已经设置该参数，则通过set的方式修改该参数。
      • 用户可以通过reset该参数的方式关闭自动创建分区功能，但是在自动删除分区功能存在的情况下，不支持关闭自动创建分区功能。
    • TTL（interval类型）

      设置分区管理中自动删除分区的分区过期时间。

      TTL的范围要求以及开启该功能的约束请参考▪TTL。

      • 在建表时，如果没有设置该参数，可以通过set的方式添加该参数，并开启自动删除分区功能；如果之前已经设置该参数，则通过set的方式修改该参数。
      • 用户可以通过reset该参数的方式关闭自动删除分区功能。
  • new_owner

    表所属新的拥有者的名字。

  • new_tablespace

    表所属新的表空间名字。

  • column_name，column_1_name， column_2_name

    现存的或新字段的名称。

  • data_type

    新字段的类型，或者现存字段的新类型。

  • compress_mode

    表字段的压缩可选项，当前仅对行存表有效。该子句指定该字段优先使用的压缩算法。

  • collation

    字段排序规则名称。可选字段COLLATE指定了新字段的排序规则，如果省略，排序规则为新字段的默认类型。

  • USING expression

    USING子句声明如何从旧的字段值里计算新的字段值；如果省略，缺省从旧类型向新类型的赋值转换。如果从旧数据类型到新类型没有隐含或者赋值的转换，则必须提供一个USING子句。

    ALTER TYPE的USING选项实际上可以声明涉及该行旧值的任何表达式，即它可以引用除了正在被转换的字段之外其他的字段。这样，就可以用ALTER TYPE语法做非常普遍性的转换。因为这个灵活性，USING表达式并没有作用于该字段的缺省值（如果有的话），结果可能不是缺省表达式要求的常量表达式。这就意味着如果从旧类型到新类型没有隐含或者赋值转换的话，即使存在USING子句，ALTER TYPE也可能无法把缺省值转换成新的类型。在这种情况下，应该用DROP DEFAULT先删除缺省，执行ALTER TYPE，然后使用SET DEFAULT增加一个合适的新缺省值。类似的考虑也适用于涉及该字段的索引和约束。

  • NOT NULL | NULL

    设置列是否允许空值。

  • integer

    带符号的整数常值。当使用PERCENT时表示按照表数据的百分比收集统计信息，integer的取值范围为0-100。

  • attribute_option

    属性选项。

  • PLAIN | EXTERNAL | EXTENDED | MAIN

    字段存储模式。

    • PLAIN必须用于定长的数值（比如integer）并且是内联的、不压缩的。
    • MAIN用于内联、可压缩的数据。
    • EXTERNAL用于外部保存、不压缩的数据。使用EXTERNAL将令在text和bytea字段上的子字符串操作更快，但付出的代价是增加了存储空间。
    • EXTENDED用于外部的压缩数据，EXTENDED是大多数支持非PLAIN存储的数据的缺省。
  • CHECK ( expression )

    每次将要插入的新行或者将要被更新的行必须使表达式结果为真才能成功，否则会抛出一个异常并且不会修改数据库。

    声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

    目前，CHECK表达式不能包含子查询也不能引用除当前行字段之外的变量。

  • DEFAULT default_expr

    给字段指定缺省值。

    缺省表达式的数据类型必须和字段类型匹配。

    缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。

    default_expr中若使用后缀操作符（如!），需使用括号括起来。

  • UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] index_parameters

    UNIQUE ( column_name [, ... ] ) [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] index_parameters

    UNIQUE约束表示表里的一个或多个字段的组合必须在全表范围内唯一。

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

    默认取值：该参数默认取值为空，即NULL值可重复插入。

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

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

    三种处理方式具体的行为如下表所示：

    表1 唯一索引中索引列NULL值的处理方式

    字段控制

    索引列全为NULL

    部分索引列为NULL

    NULLS DISTINCT

    可重复插入

    可重复插入

    NULLS NOT DISTINCT

    不可重复插入

    非NULL值相等，不可插入；非NULL值不相等，则插入成功

    NULLS IGNORE

    可重复插入

    非NULL值相等，不可插入；非NULL值不相等，则插入成功
  • PRIMARY KEY index_parameters

    PRIMARY KEY ( column_name [, ... ] ) index_parameters

    主键约束表明表中的一个或者一些字段只能包含唯一（不重复）的非NULL值。

  • DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE

    设置该约束是否可推迟，列存暂不支持。

    • DEFERRABLE：可以推迟到事务结尾使用SET CONSTRAINTS命令检查。
    • NOT DEFERRABLE：在每条命令之后马上检查。
    • INITIALLY IMMEDIATE：那么每条语句之后就立即检查它。
    • INITIALLY DEFERRED：只有在事务结尾才检查它。
  • WITH ( {storage_parameter = value} [, ... ] )

    为表或索引指定一个可选的存储参数。

  • COMPRESS|NOCOMPRESS
    • NOCOMPRESS：如果指定关键字NOCOMPRESS则不会修改表的现有压缩特性。
    • COMPRESS：如果指定COMPRESS关键字，则对该表进行批量插入元组时触发该特性。
  • new_table_name

    修改后新的表名称。

  • new_column_name

    表中指定列修改后新的列名称。

  • new_constraint_name

    修改后表约束的新名称。

  • new_schema

    修改后新的模式名称。

  • CASCADE

    级联删除依赖于被依赖字段或者约束的对象（比如引用该字段的视图）。

  • RESTRICT

    如果字段或者约束还有任何依赖的对象，则拒绝删除该字段。这是缺省行为。

  • schema_name

    表所在的模式名称。

表操作示例

重命名表：

1
 
ALTER TABLE CUSTOMER RENAME TO CUSTOMER_t;

给表增加一个新的约束：

1
 
ALTER TABLE customer_address ADD PRIMARY KEY(ca_address_sk);

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

先给表CUSTOMER创建唯一索引CUSTOMER_constraint1，然后根据已有唯一索引增加主键约束，并对前面创建的索引rename：

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 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_address ADD CONSTRAINT customer_address_cluster PARTIAL CLUSTER KEY(ca_address_sk);

删除一个列存表中的局部聚簇列：

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_address SET SCHEMA tpcds;

单表冷热切换：

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
 
ALTER TABLE warehouse_t ADD W_GOODS_CATEGORY int;

修改表中列名信息以及列字段信息：

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

给已创建好的表增加主键：

1
 
ALTER TABLE warehouse_t ADD PRIMARY KEY(w_warehouse_name);

重命名列：

1
 
ALTER TABLE CUSTOMER RENAME C_PHONE TO new_C_PHONE;

向表中增加多列：

1
 
ALTER TABLE CUSTOMER ADD (C_COMMENT VARCHAR(117) NOT NULL, C_COUNT int);

修改表中已存在字段的数据类型，并将字段约束设置为非空：

1
 
ALTER TABLE CUSTOMER MODIFY C_MKTSEGMENT varchar(20) NOT NULL;

为表的某列添加not null约束：

1
 
ALTER TABLE CUSTOMER ALTER COLUMN C_PHONE SET NOT NULL;

从表中删除一个字段：

1
 
ALTER TABLE CUSTOMER DROP COLUMN C_COUNT;

给表中某一列添加索引：

1
 
ALTER TABLE customer_address MODIFY ca_address_id varchar(20) CONSTRAINT ca_address_index CHECK (ca_address_id > 0);

向customer_address表中增加一个带有on update的timestamp列：

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

向customer_address表中将带有on update的timestamp列删除

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

相关链接

CREATE TABLE，12.101-RENAME TABLE，DROP TABLE

父主题： DDL语法