openGauss SQL语法 —ALTER TABLE（4）

参数说明

• IF EXISTS

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

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

  table_name是需要修改的表名。

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

• constraint_name

  • 在DROP CONSTRAINT操作中表示要删除的现有约束的名称。

  • 在ADD CONSTRAINT操作中表示新增的约束名称。

    

     须知：

    对于新增约束，在B模式数据库下（即sql_compatibility = 'B'）constraint_name为可选项，在其他模式数据库下，必须加上constraint_name。

• index_name

  索引名称。

  

   须知：

  在ADD CONSTRAINT操作中：

  • index_name仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库下不支持。
  • 对于外键约束，constraint_name和index_name同时指定时，索引名为constraint_name。
  • 对于唯一键约束，constraint_name和index_name同时指定时，索引名以index_name。

• USING method

  指定创建索引的方法。

  取值范围参考参数说明中的USING method。

  

   须知：

  在ADD CONSTRAINT操作中：

  • USING method仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库下不支持。
  • 在B模式下，未指定USING method时，对于ASTORE的存储方式，默认索引方法为btree；对于USTORE的存储方式，默认索引方法为ubtree。

• ASC | DESC

  ASC表示指定按升序排序（默认）。DESC指定按降序排序。

  

   须知：

  在ADD CONSTRAINT中，ASC|DESC只在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库不支持。

• expression

  创建一个基于该表的一个或多个字段的表达式索引约束，必须写在圆括弧中。

  

   须知：

  表达式索引只在B模式数据库下支持（即sql_compatibility = 'B'），其他模式数据库不支持。

• storage_parameter

  表的存储参数的名称。

  创建索引新增一个选项：

  • parallel_workers（int类型）

    取值范围：[0,32]，0表示关闭并发。

    表示创建索引时起的bgworker线程数量，例如2就表示将会起2个bgworker线程并发创建索引。

    如果未设置，启动bgworker线程数量与表大小相关，一般不超过4个线程。

  • hasuids（bool类型）

    默认值：off

    参数开启：更新表元组时，为元组分配表级唯一标识id。

• new_owner

  表新拥有者的名称。

• new_tablespace

  表所属新的表空间名称。

• column_name、column_1_name、 column_2_name

  现存的或新字段的名称。

• data_type

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

• compress_mode

  表字段的压缩可选项。该子句指定该字段优先使用的压缩算法。行存表不支持压缩。

• charset

  只在B模式数据库下（即sql_compatibility = 'B'）支持该语法，其他模式数据库不支持。指定表字段的字符集，单独指定时会将字段的字符序设置为指定的字符集的默认字符序。

• collation

  字段排序规则（字符序）名称。可选字段COLLATE指定了新字段的排序规则，如果省略，排序规则为新字段的默认类型。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

  对于B模式数据库下（即sql_compatibility = 'B'）还支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary字符序，部分说明见表字段的字符集说明（参见表1 B模式（即sql_compatibility = 'B'）下支持的字符集和字符序介绍）。

  

   说明：

  • 仅字符类型支持指定字符集，指定为binary字符集或字符序实际是将字符类型转化为对应的二进制类型，若类型映射不存在则报错。当前仅有TEXT类型转化为BLOB的映射。
  • 除binary字符集和字符序外，当前仅支持指定与数据库编码相同的字符集。
  • 未显式指定字段字符集或字符序时，若指定了表的默认字符集或字符序，字段字符集和字符序将从表上继承。若表的默认字符集或字符序不存在，当b_format_behavior_compat_options = 'default_collation'时，字段的字符集和字符序将继承当前数据库的字符集及其对应的默认字符序。
  • 当修改的字符集或字符序对应的字符集与当前字段字符集不同时，会将字段中的数据转换为指定的字符集进行编码。

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

• GENERATED ALWAYS AS ( generation_expr ) [STORED]

  该子句将字段创建为生成列，生成列的值在写入（插入或更新）数据时由generation_expr计算得到，STORED表示像普通列一样存储生成列的值。

  

   说明：

  • STORED关键字可省略，与不省略STORED语义相同。
  • 生成表达式不能以任何方式引用当前行以外的其他数据。生成表达式不能引用其他生成列，不能引用系统列。生成表达式不能返回结果集，不能使用子查询，不能使用聚集函数，不能使用窗口函数。生成表达式调用的函数只能是不可变（IMMUTABLE）函数。
  • 不能为生成列指定默认值。
  • 生成列不能作为分区键的一部分。
  • 生成列不能和ON UPDATE约束字句的CASCADE,SET NULL,SET DEFAULT动作同时指定。生成列不能和ON DELETE约束字句的SET NULL,SET DEFAULT动作同时指定。
  • 修改和删除生成列的方法和普通列相同。删除生成列依赖的普通列，生成列被自动删除。不能改变生成列所依赖的列的类型。
  • 生成列不能被直接写入。在INSERT或UPDATE命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。
  • 生成列的权限控制和普通列一样。
  • 列存表、内存表MOT不支持生成列。外表中仅postgres_fdw支持生成列。

• UNIQUE [KEY] index_parameters

  UNIQUE ( column_name [ ( length ) ] [, … ] ) index_parameters

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

  UNIQUE KEY只能在sql_compatibility='B'时使用，与UNIQUE语义相同。

  column_name(length)是前缀键，详见：前缀键说明。

• PRIMARY KEY index_parameters

  PRIMARY KEY ( column_name [, … ] ) index_parameters

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

• REFERENCES reftable [ ( refcolum ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)

  FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

  外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。若省略refcolum，则将使用reftable的主键。被参考列应该是被参考表中的唯一字段或主键。外键约束不能被定义在临时表和永久表之间。

  参考字段与被参考字段之间存在三种类型匹配，分别是：

  • MATCH FULL：不允许一个多字段外键的字段为NULL，除非全部外键字段都是NULL。
  • MATCH SIMPLE（缺省）：允许任意外键字段为NULL。
  • MATCH PARTIAL：目前暂不支持。

  另外，当被参考表中的数据发生改变时，某些操作也会在新表对应字段的数据上执行。ON DELETE子句声明当被参考表中的被参考行被删除时要执行的操作。ON UPDATE子句声明当被参考表中的被参考字段数据更新时要执行的操作。对于ON DELETE子句、ON UPDATE子句的可能动作：

  • NO ACTION（缺省）：删除或更新时，创建一个表明违反外键约束的错误。若约束可推迟，且若仍存在任何引用行，那这个错误将会在检查约束的时候产生。
  • RESTRICT：删除或更新时，创建一个表明违反外键约束的错误。与NO ACTION相同，只是动作不可推迟。
  • CASCADE：删除新表中任何引用了被删除行的行，或更新新表中引用行的字段值为被参考字段的新值。
  • SET NULL：设置引用字段为NULL。
  • SET DEFAULT：设置引用字段为它们的缺省值。

• DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE

  设置该约束是否可推迟。

  • DEFERRABLE：可以推迟到事务结尾使用SET CONSTRAINTS命令检查。

  • NOT DEFERRABLE：在每条命令之后马上检查。

  • INITIALLY IMMEDIATE：那么每条语句之后就立即检查它。

  • INITIALLY DEFERRED：只有在事务结尾才检查它。

• PARTIAL CLUSTER KEY

  局部聚簇存储，列存表导入数据时按照指定的列(单列或多列)，进行局部排序。

• WITH ( {storage_parameter = value} [, … ] )

  为表或索引指定一个可选的存储参数，详见CREATE TABLE语法相关字段的介绍。

  

   说明：

  • 行存表支持修改行存压缩参数，包括COMPRESSTYPE、COMPRESS_LEVEL、COMPRESS_CHUNK_SIZE、COMPRESS_PREALLOC_CHUNKS、COMPRESS_BYTE_CONVERT、COMPRESS_DIFF_CONVERT，修改会对表做重建，修改后对原有数据、修改对已有数据、变更数据、新增数据同时生效。（仅支持ASTORE和USTORE下的普通表和分区表）
  • 修改行存压缩参数时，修改后的行存压缩参数需要满足建表时各行存压缩参数的数据范围和参数间的约束。
  • 分区表不支持修改分区级别的行存压缩参数，只能修改整个表的行存压缩属性，修改对所有分区生效。
  • 修改行存压缩参数时会重写整个表，期间对表加八级锁。
  • 修改行存压缩参数时会重建表， 如果表的数据库较大，该过程可能花费较长时间
  • 重建表期间openGauss会先生成新的数据文件，再删除旧的数据文件，需要事先保证有足够的空闲物理空间。

• tablespace_name

  索引所在表空间的名称。

• COMPRESS|NOCOMPRESS

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

• new_table_name

  修改后新的表名称。

• new_column_name

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

• new_constraint_name

  修改后表约束的新名称。

• new_schema

  修改后新的模式名称。

• CASCADE

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

• RESTRICT

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

• FIRST

  新增列或修改列到第一位。

• AFTER column_name

  新增列或修改列到column_name之后。

  

   说明：

  • 列存表不支持FIRST | AFTER column_name。
  • 仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库不支持。
  • 加密列不支持FIRST | AFTER column_name。
  • 有规则依赖的表不支持改变表列的位置（包括新增和修改导致列位置的变化）。
  • 外表不支持FIRST | AFTER column_name。
  • SET类型的字段不支持修改到指定位置。

• schema_name

  表所在的模式名称。

• VISIBLE | INVISIBLE

  指定索引是否可见，如果没有声明则默认为VISIBLE。

• [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset

  仅在sql_compatibility='B'时支持该语法。修改表的默认字符集，单独指定时会将表的默认字符序设置为指定的字符集的默认字符序。

• [DEFAULT] COLLATE [ = ] default_collation

  仅在sql_compatibility='B'时支持该语法。修改表的默认字符序，单独指定时会将表的默认字符集设置为指定的字符序对应的字符集。字符序参见表1 B模式（即sql_compatibility = 'B'）下支持的字符集和字符序介绍。

  

   说明： 未显式指定表的字符集或字符序时，若指定了模式的默认字符集或字符序，表字符集和字符序将从模式上继承。若模式的默认字符集或字符序不存在，当b_format_behavior_compat_options = 'default_collation'时，表的字符集和字符序将继承当前数据库的字符集及其对应的默认字符序。