# ALTER TABLE #### 功能描述 修改表，包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关。 只有表的所有者或者被授予了表ALTER权限的用户有权限执行ALTER TABLE命令，系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式，当前用户必须是该表的所有者或者系统管理员，且该用户是新的所有角色的直接或间接成员。 #### 注意事项 表1ALTER TABLE注意事项 | 约束项 | 详细内容 | |:---|:---| | 不支持修改表数据的存储方式 | 建表后不支持修改存储参数ORIENTATION（该参数指定表为行存或列存）。 | | 列存表约束支持范围 | 列存表支持PARTIAL CLUSTER KEY，不支持外键表级约束。列存表自8.1.1集群版本开始建表时支持创建主键和唯一表级约束。 | | 列存表约束修改限制 | - 支持通过ALTER方式对已有列存表添加非空约束NOT NULL以及主键约束。该约束仅8.2.0及以上集群版本支持。 - 通过约束名删除PRIMARY KEY约束时，不会删除NOT NULL约束。如有需要，可手动删除NOT NULL约束。 | | 列存表的字段约束 | 列存表支持的字段约束包括NULL、NOT NULL和DEFAULT常量值；对字段约束的修改，当前支持对DEFAULT值的修改（SET DEFAULT）、删除（DROP DEFAULT）和NOT NULL约束的删除。 | | 列存表支持操作 | - 添加字段ADD COLUMN，对于添加的字段和修改的字段类型要求是列存支持的[数据类型](https://support.huaweicloud.com/sqlreference-dws/dws_06_0008.html)。 - 修改字段的数据类型ALTER TYPE，ALTER TYPE的USING选项只支持常量表达式和涉及本字段的表达式，暂不支持涉及其他字段的表达式。 - 设置单个字段的收集目标SET STATISTICS。 - 支持更改表名称。 - 支持删除字段DROP COLUMN。 | | 分布列修改限制 | 不支持修改已有表的分布列数据类型。 | | 行存REPLICATION表限制 | 行存REPLICATION分布表不支持将系统列设置为主键。 | | 不支持增加自增列 | 不支持增加自增列，或者增加DEFAULT值中包含nextval()表达式的列。 | | OBS冷热表修改约束 | - OBS冷热表不支持ALTER RESET的cold_tablespace，storage_policy参数，不支持修改COLVERSION为1.0。 - 可将原列存COLVERSION为2.0表ALTER为OBS冷热表，需同时增加cold_tablespace和storage_policy参数。 - ALTER TABLE支持针对RELOPTIONS的storage_policy的选项更改。冷热切换策略发生更改后，不改变现有冷数据的冷热属性，只改变下一次执行冷热切换的规则，下一次执行冷热切换命令时将按新规则进行冷热切换。 | | V3表 | V3表（COLVERSION为3.0）不支持修改COLVERSION，也不支持将非V3表切换为V3表（即COLVERSION为2.0不支持切为3.0）。 | | 触发表重建的操作 | 对表执行以下ALTER TABLE操作时会触发表重建（表重建过程中会先把数据转储到一个新的数据文件中，重建完成之后会删除原始文件），当表比较大时，重建会消耗较多的磁盘空间。当磁盘空间不足时，要谨慎对待大表ALTER TABLE操作，防止触发集群只读。 - 修改列数据类型。 - 行存表增加列（包括oid列）。 - 列存表修改COLVERSION。 - 列存表增加列指定DEFAULT常量值，DEFAULT值中包含volatile函数以及DEFAULT值非NULL且不属于特定数据类型中的任何一个。 | | 其他 | - 修改列存表存储参数COLVERSION或者enable_delta时，不能与其他ALTER操作同时进行。 - 对unlogged表执行ALTER TABLE时，不允许指定表空间。对非unlogged表执行ALTER TABLE时，不允许指定表空间为pg_unlogged。 - 全局临时表不支持修改分布列。 | ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/warning_3.0-zh-cn.png) - 避免在业务高峰期执行ALTER TABLE/ALTER TABLE PARTITION（增删改查、DROP PARTITION）、TRUNCATE操作，避免有长SQL阻塞ALTER、TRUNCATE操作或SQL业务被ALTER、TRUNCATE阻塞。 - 更多开发设计规范参见[总体开发设计规范](https://support.huaweicloud.com/devg-dws/dws_04_0075.html)。 #### ALTER TABLE语法格式 ``` 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名；如果new_table_name不指定schema，则表示new_table_name与原表处于同一schema中。 ``` ALTER TABLE [ IF EXISTS ] table_name RENAME TO new_table_name; ALTER TABLE [ IF EXISTS ] table_name RENAME TO schema.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; ``` ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) - SET SCHEMA操作不支持修改为系统内部模式，当前仅支持用户模式之间的修改。 - 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列，需要将序列删除，重建，或者取消拥有关系， 才能将表schema更改成功。 - 要修改一个表的模式，用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表，用户必须同时又是父表的所有者。要修改所有者，用户还必须是新的所有角色的直接或间接成员，并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能执行重建和删除表以外的操作，但系统管理员能以任何方式修改任意表的所有权限。 - 添加多个列。 ``` 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 table_name DROP INDEX index_name; ``` ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) - 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如，可以在一个命令里增加几个字段或修改几个字段的类型。对于大表，此种操作带来的效率提升更明显，原因在于只需要对该大表做一次处理。 - 增加一个CHECK或NOT NULL约束将会扫描该表，以保证现有的行符合约束要求。 - 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说，这个操作可能会花很长时间，并且它还临时需要两倍的磁盘空间。 #### 表操作相关的action子句 具体表操作action可以是以下子句之一： ``` 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，以便收集全新的统计信息。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) - 本操作属于重大变更操作，涉及表分布信息的修改以及数据的物理重分布，修改过程中会阻塞业务，修改完成后原有业务的执行计划会发生变化，请按照正规变更流程进行。 - 本操作属于资源密集操作，针对大表的分布方式修改，建议在计算和存储资源充裕情况下进行，保证整个集群和原表所在表空间有足够的剩余空间能存储一张与原表同等大小且按照新分布方式进行分布的表。 - **TO { GROUP groupname \| NODE ( nodename \[, ... \] ) }** 此语法仅在扩展模式（GUC参数enable_cluster_resize为on时）下可用。该模式谨慎打开，主要供内部扩容工具使用，一般用户不应使用该模式。 - **ADD NODE ( nodename \[, ... \] )** 此语法主要供内部扩容工具使用，一般用户不建议使用。 - **DELETE NODE ( nodename \[, ... \] )** 此语法主要供内部缩容工具使用，一般用户不建议使用。 - **DISABLE TRIGGER \[ trigger_name \| ALL \| USER \]** 禁用trigger_name所表示的单个触发器，或禁用所有触发器，或仅禁用用户触发器（此选项不包括内部生成的约束触发器，例如，可延迟唯一性和排除约束的约束触发器）。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) 应谨慎使用此功能，因为如果不执行触发器，则无法保证原先期望的约束的完整性。 - **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](https://support.huaweicloud.com/sqlreference-dws/dws_06_0169.html)章节。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/note_3.0-zh-cn.png) 不支持对HDFS表、外表、临时表开启行访问控制开关。 - **NO FORCE/FORCE ROW LEVEL SECURITY** 强制开启或关闭表的行访问控制开关。 默认情况，表所有者不受行访问控制特性影响，但当强制开启表的行访问控制开关时，表的所有者（不包含系统管理员用户）会受影响。系统管理员可以绕过所有的行访问控制策略，不受影响。 - **REFRESH STORAGE** 根据OBS冷热表storage_policy所定义的规则，将符合条件的本地热分区切换为存储在OBS上的冷分区。 例如创建OBS冷热表时，设置storage_policy为 'LMT:10'，则在执行该操作时可将10日前无修改的分区切为冷存储，存至OBS中。 #### 列相关的操作column_clause子句 其中列相关的操作column_clause可以是以下子句之一： ``` 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要求重写整个表的特性有时候是一个优点，因为重写的过程消除了表中没用的空间。比如，要想立刻回收被一个已经删除的字段占据的空间，最快的方法是： ``` ALTER TABLE table ALTER COLUMN anycol TYPE anytype; ``` 这里的anycol是任何在表中还存在的字段，而anytype是和该字段的原类型一样的类型。这样的结果是在表上没有任何可见的语义的变化，但是这个命令强制重写，这样就删除了不再使用的数据。 ALTER TYPE的USING选项实际上可以声明涉及该行旧值的任何表达式，即可引用正在被转换的字段之外的其他字段。因此可以用ALTER TYPE语法实现非常通用的类型转换。由于这种灵活性，USING表达式不会作用于该字段的缺省值（如果有的话），因此其结果可能不是缺省表达式要求的常量表达式。这意味着，如果从旧类型到新类型没有隐式或赋值转换，即使存在USING子句，ALTER TYPE也可能无法将缺省值转换为新的类型。这种情况下，应先用DROP DEFAULT删除缺省，再执行ALTER TYPE，最后使用SET DEFAULT设置新的缺省值。类似的问题也适用于涉及该字段的索引和约束。 - **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](https://support.huaweicloud.com/sqlreference-dws/dws_06_0165.html)。 - **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](https://support.huaweicloud.com/sqlreference-dws/dws_06_0165.html)。 - **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为： ``` [ 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为： ``` [ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS ] ``` - 根据已有唯一索引为表增加主键约束或唯一约束table_constraint_using_index为： ``` [ CONSTRAINT constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ] ``` - 增加外键约束REFERENCES为： ``` [ CONSTRAINT constraint_name ] FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] } ``` - 表约束table_constraint为： ``` [ 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为： ``` [ WITH ( {storage_parameter = value} [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ] ``` #### 参数说明 表2ALTER TABLE参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | IF EXISTS | 如果表名不存在时返回一个提示而不是错误。 | - | | ONLY | 仅对指定的表名执行操作。 如果不使用ONLY关键字， 该表及其所有子表都将会被更改。 需注意：只允许在父表或子表中添加或删除列，或更改列的类型。 父表及其后代表必须始终具有相同的列和类型。 | - | | table_name | 需要修改的表名称。 可以在表名称后面显式地增加\*选项来指定包括子表，即表示所有后代表都被扫描，这是默认行为。 | 有效的表名。 | | new_table_name | 表的新名称。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | column_name，column_1_name， column_2_name | 现有的或新字段的名称。 | 有效的字段名。 | | new_column_name | 指定列的新名称。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | constraint_name | 约束的名称。 | 有效的约束名。 | | new_constraint_name | 表约束的新名称。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | schema_name | 表所在的模式名称。 | 有效的模式名 | | new_schema | 修改后新的模式名称。 | 有效的模式名。 | | index_name | 索引名称。 | 有效的索引名。 | | new_owner | 表的新所有者的名称。 | 有效的用户名或角色名。 | | new_tablespace | 表所属的新表空间的名称。 | 有效的表空间名。 | | data_type | 新字段的类型，或者现有字段的新类型。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | storage_parameter | 表的存储参数。 | 参见[表3]。 | | compress_mode | 表字段的压缩可选项，当前仅对行存表有效。该子句指定该字段优先使用的压缩算法 | - | | collation | 字段排序规则名称。可选字段COLLATE指定了新字段的排序规则，如果省略，排序规则为新字段的默认类型。 | - | | USING expression | USING子句声明如何从旧的字段值里计算新的字段值；如果省略，缺省从旧类型向新类型的赋值转换。如果从旧数据类型到新类型没有隐含或者赋值的转换，则必须提供一个USING子句。 | - | | NOT NULL \| NULL | 设置列是否允许空值。 | - | | integer | 带符号的整数常量。当使用PERCENT时表示按照表数据的百分比收集统计信息。 | 0\~100 | | attribute_option | 属性选项。 | - | | PLAIN \| EXTERNAL \| EXTENDED \| MAIN | 字段存储模式。 - PLAIN必须用于定长的数值（比如integer）并且是内联的、不压缩的。 - MAIN用于内联、可压缩的数据。 - EXTERNAL用于外部保存、不压缩的数据。使用EXTERNAL将令在text和bytea字段上的子字符串操作更快，但付出的代价是增加了存储空间。 - EXTENDED用于外部的压缩数据，EXTENDED是大多数支持非PLAIN存储的数据的缺省。 | - | | CHECK ( expression ) | 每次将要插入的新行或者将要被更新的行必须使表达式结果为真才能成功，否则会抛出一个异常并且不会修改数据库。 声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。 目前，CHECK表达式不能包含子查询也不能引用除当前行字段之外的变量。 | - | | DEFAULT default_expr | 给字段指定缺省值。 缺省表达式的数据类型必须和字段类型匹配。 缺省表达式将被用于任何未声明该字段数值的插入操作。若未显式指定DEFAULT，则默认值为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值的处理方式。具体可参考[表4]。 默认取值：该参数默认取值为空，即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关键字，则对该表进行批量插入元组时触发该特性。 | - | | CASCADE | 级联删除依赖于被依赖字段或者约束的对象（比如引用该字段的视图）。 | - | | RESTRICT | 如果字段或者约束还有任何依赖的对象，则拒绝删除该字段。这是缺省行为。 | - | | cache_policy | 表缓存策略，仅存算分离3.0版本支持。 默认值为ALL。 | - ALL： 对整个表进行热缓存。 - NONE：对整个表进行冷缓存。 - HPN：N分区表中前N个分区会被热缓存，其余分区进行冷缓存。 - HPL：P1, P2, ... 分区表中在缓存策略中被指定名称的分区会被热缓存，其余分区进行冷缓存。 说明： - 对于外表和非分区内表只支持ALL和NONE两种缓存策略。 - 仅range和list分区内表支持HPN和HPL缓存策略。 | 表3storage_parameter参数说明 | **storage_parameter** 参数 | 参数项 | 描述 | 取值范围 | |:---|:---|:---|:---| | 分区管理新增的两个选项 | PERIOD | 设置分区管理中自动创建分区的周期。 说明： - 在建表时，如果没有设置该参数，可以通过set的方式添加该参数，并开启自动创建分区功能；如果之前已经设置该参数，则通过set的方式修改该参数。 - 用户可以通过reset该参数的方式关闭自动创建分区功能，但是在自动删除分区功能存在的情况下，不支持关闭自动创建分区功能。 | interval类型。 PERIOD的范围要求以及开启该功能的约束请参考[WITH子句参数说明](https://support.huaweicloud.com/sqlreference-dws/dws_06_0179.html#ZH-CN_TOPIC_0000001764675414__section183201214104111)中的PERIOD参数。 | | 分区管理新增的两个选项 | TTL | 设置分区管理中自动删除分区的分区过期时间。 说明： - 在建表时，如果没有设置该参数，可以通过set的方式添加该参数，并开启自动删除分区功能；如果之前已经设置该参数，则通过set的方式修改该参数。 - 用户可以通过reset该参数的方式关闭自动删除分区功能。 | interval类型。 TTL的范围要求以及开启该功能的约束请参考[WITH子句参数说明](https://support.huaweicloud.com/sqlreference-dws/dws_06_0179.html#ZH-CN_TOPIC_0000001764675414__section183201214104111)中的TTL参数。 | | turbo存储格式列存表新增的选项 | enable_turbo_store | 设置列存表是否为turbo存储格式。该选项仅9.1.0.100及以上集群版本支持。 说明： - 3.0普通列存表不支持设置turbo存储格式，仅3.0 hstore_opt表支持turbo存储格式。 - 2.0版本列存表无限制。 | - | 唯一索引中索引列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值不相等，才可成功插入数据。 表4唯一索引中索引列NULL值的处理方式 | 字段控制 | 索引列全为NULL | 部分索引列为NULL | |:---|:---|:---| | NULLS DISTINCT | 可重复插入 | 可重复插入 | | NULLS NOT DISTINCT | 不可重复插入 | 非NULL值相等，不可插入；非NULL值不相等，则插入成功 | | NULLS IGNORE | 可重复插入 | 非NULL值相等，不可插入；非NULL值不相等，则插入成功 | #### 表操作示例 创建示例表customer。 ``` 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。 ``` 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。 ``` CREATE SCHEMA myschema; ALTER TABLE customer SET SCHEMA myschema; ``` - 根据已有唯一索引为表增加主键约束或唯一约束。 为表customer创建唯一索引customer_constraint1，然后根据已有唯一索引增加主键约束，并对前面创建的索引进行重命名。 ``` CREATE UNIQUE INDEX customer_constraint1 ON myschema.customer(C_CUSTKEY); ALTER TABLE myschema.customer ADD CONSTRAINT customer_constraint2 PRIMARY KEY USING INDEX customer_constraint1; ``` - 重命名表约束。 ``` ALTER TABLE myschema.customer RENAME CONSTRAINT customer_constraint2 TO customer_constraint; ``` - 删除表约束。 ``` ALTER TABLE myschema.customer DROP CONSTRAINT customer_constraint; ``` - 给表新增主键约束。 ``` ALTER TABLE myschema.customer ADD PRIMARY KEY(C_CUSTKEY); ``` - 向列存表customer_address中添加局部聚簇列。 ``` ALTER TABLE customer_address ADD CONSTRAINT customer_address_cluster PARTIAL CLUSTER KEY(ca_address_sk); ``` - 删除列存表customer_address中的局部聚簇列。 ``` ALTER TABLE customer_address DROP CONSTRAINT customer_address_cluster; ``` - 修改列存表的存储格式。**注意** ：实时数仓Hstore表不支持该示例。 ``` ALTER TABLE customer_address SET (COLVERSION = 1.0); ``` - 修改表的分布方式。 ``` ALTER TABLE customer_address DISTRIBUTE BY REPLICATION; ``` - 重命名表。 ``` ALTER TABLE myschema.customer RENAME TO customer_t; ``` - 修改表缓存策略（仅存算分离3.0版本集群支持）。 ``` DROP TABLE IF EXISTS orders; CREATE TABLE orders(a int, b int); ALTER TABLE orders SET (cache_policy = 'NONE'); ``` - 关闭表自动删除分区功能。创建表的示例可参考[示例：创建指定分区的自动分区管理分区表](https://support.huaweicloud.com/sqlreference-dws/dws_06_0179.html#ZH-CN_TOPIC_0000001764675414__section12898122016183)。 ``` DROP TABLE IF EXISTS CPU; CREATE TABLE CPU( id integer, idle numeric, IO numeric, scope text, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2022-01-05 16:32:45'), PARTITION P2 VALUES LESS THAN('2022-01-06 16:56:12') ); ALTER TABLE CPU RESET (ttl); ``` #### 索引操作示例 - 给表增加一个索引。 ``` ALTER TABLE myschema.customer_t ADD INDEX customer_index(C_CUSTKEY); ``` - 删除表索引。 ``` ALTER TABLE myschema.customer_t DROP INDEX customer_index; ALTER TABLE myschema.customer_t ADD INDEX customer_index(C_CUSTKEY); ALTER TABLE myschema.customer_t DROP KEY customer_index; ``` - 给表中某一列添加索引。 ``` ALTER TABLE myschema.customer_t ADD c_address_id varchar(20) CONSTRAINT ca_address_index CHECK (c_address_id > 0); ``` #### 冷热表操作示例 - 单表冷热切换。 ``` DROP TABLE IF EXISTS lifecycle_table; CREATE TABLE lifecycle_table(i int, val text) WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:100') PARTITION BY RANGE (i) ( PARTITION P1 VALUES LESS THAN(5), PARTITION P2 VALUES LESS THAN(10), PARTITION P3 VALUES LESS THAN(15), PARTITION P8 VALUES LESS THAN(MAXVALUE) ) ENABLE ROW MOVEMENT; ALTER TABLE lifecycle_table REFRESH STORAGE; ``` - 列存分区表修改为冷热表。 ``` DROP TABLE IF EXISTS test_1; 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'); ``` #### 列操作示例 创建示例表warehouse_t。 ``` 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中增加单列。 ``` ALTER TABLE warehouse_t ADD W_GOODS_CATEGORY int; ``` 向表warehouse_t中增加多列。 ``` ALTER TABLE warehouse_t ADD (W_COMMENT VARCHAR(117) NOT NULL, W_COUNT int); ``` - 删除列。 从表warehouse_t中删除一列。 ``` ALTER TABLE warehouse_t DROP COLUMN W_STATE; ``` - 重命名列。 表warehouse_t的列名W_ZIP修改为new_W_ZIP。 ``` ALTER TABLE warehouse_t RENAME W_ZIP TO new_W_ZIP; ``` - 变更列名同时修改列字段信息。 表warehouse_t的列名W_GOODS_CATEGORY修改为W_GOODS_CATEGORY2，同时修改W_GOODS_CATEGORY2字段数据类型为DECIMAL，字段约束为非空。 ``` ALTER TABLE warehouse_t CHANGE W_GOODS_CATEGORY W_GOODS_CATEGORY2 DECIMAL NOT NULL COMMENT 'W_GOODS_CATEGORY'; ``` - 变更列的注释信息（COMMENT）。 ``` ALTER TABLE warehouse_t MODIFY W_GOODS_CATEGORY2 COMMENT 'W_GOODS_CATEGORY'; ``` - 变更列的数据类型。 修改表字段warehouse_t的数据类型，并将字段约束设置为非空。 ``` ALTER TABLE warehouse_t MODIFY W_WAREHOUSE_SQ_FT varchar(20) NOT NULL; ``` - 指定列添加非空约束（not null）。 ``` ALTER TABLE warehouse_t ALTER COLUMN W_COUNTY SET NOT NULL; ``` - 为表warehouse_t增加主键。 ``` ALTER TABLE warehouse_t ADD PRIMARY KEY(w_warehouse_name); ``` - 修改表中指定列的on update表达式。 为warehouse_t表增加一个带有on update的timestamp列，在表执行UPDATE操作时自动更新该列的时间戳。 ``` ALTER TABLE warehouse_t ADD COLUMN C_TIME timestamp on update current_timestamp; ``` 将warehouse_t表中带有on update的timestamp列删除。 ``` ALTER TABLE warehouse_t MODIFY COLUMN C_TIME timestamp on update NULL; ``` #### 相关链接 [CREATE TABLE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0177.html)，[12.101-RENAME TABLE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0276.html)，[DROP TABLE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0208.html) #### 常见问题 [如何调整DWS分布列？](https://support.huaweicloud.com/dws_faq/dws_03_2126.html) [数据类型转换出现报错：invalid input syntax for xxx](https://support.huaweicloud.com/trouble-dws/dws_09_0037.html) [向表中插入数据报错：duplicate key value violates unique constraint "%s](https://support.huaweicloud.com/trouble-dws/dws_09_0123.html)