ALTER TABLE

    注意事项

    • 表的所有者被授予了表ALTER权限的用户或被授予ALTER ANY TABLE的用户有权限执行ALTER TABLE命令,系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式,当前用户必须是该表的所有者或者系统管理员,且该用户是新所有者角色的成员。
    • 不能修改分区表的tablespace,但可以修改分区的tablespace。
    • 不支持修改存储参数ORIENTATION。
    • SET SCHEMA操作不支持修改为系统内部模式,当前仅支持用户模式之间的修改。
    • 列存表只支持PARTIAL CLUSTER KEY、UNIQUE、PRIMARY KEY表级约束,不支持外键等表级约束。
    • 列存表只支持添加字段ADD COLUMN、修改字段的数据类型ALTER TYPE、设置单个字段的收集目标SET STATISTICS、支持更改表名称、支持更改表空间、支持删除字段DROP COLUMN。对于添加的字段和修改的字段类型要求是列存支持的数据类型。ALTER TYPE的USING选项只支持常量表达式和涉及本字段的表达式,暂不支持涉及其他字段的表达式。
    • 列存表支持的字段约束包括NULL、NOT NULL、DEFAULT常量值、UNIQUE和PRIMARY KEY;对字段约束的修改当前只支持对DEFAULT值的修改(SET DEFAULT)和删除(DROP DEFAULT),暂不支持对非空约束NULL/NOT NULL的修改。
    • 不支持增加自增列,或者增加DEFAULT值中包含nextval()表达式的列。
    • 不支持对外表、临时表开启行访问控制开关。
    • 通过约束名删除PRIMARY KEY约束时,不会删除NOT NULL约束,如果有需要,请手动删除NOT NULL约束。
    • 使用JDBC时,支持通过PrepareStatement对DEFAULT值进行参数化设置。
    • 重命名时,不能与当前命名空间的synonym产生命名冲突。
    • 设置命名空间时,不能与当前命名空间的synonym产生命名冲突。
    • 仅支持在B兼容性数据库下指定COMMENT和可见性VISIBLE\INVISIBLE。
    • 使用FIRST | AFTER column_name新增列或修改列,或修改字段的字符集,会带来全表更新开销,影响在线业务。
    • 修改表的定义。

      其中具体表操作action可以是以下子句之一:

      1. | ADD table_constraint [ NOT VALID ]
      2. | ADD table_constraint_using_index
      3. | VALIDATE CONSTRAINT constraint_name
      4. | DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
      5. | CLUSTER ON index_name
      6. | SET WITHOUT CLUSTER
      7. | SET ( {storage_parameter = value} [, ... ] )
      8. | RESET ( storage_parameter [, ... ] )
      9. | OWNER TO new_owner
      10. | SET TABLESPACE new_tablespace
      11. | SET {COMPRESS|NOCOMPRESS}
      12. | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
      13. | ADD NODE ( nodename [, ... ] )
      14. | DELETE NODE ( nodename [, ... ] )
      15. | DISABLE TRIGGER [ trigger_name | ALL | USER ]
      16. | ENABLE TRIGGER [ trigger_name | ALL | USER ]
      17. | ENABLE REPLICA TRIGGER trigger_name
      18. | ENABLE ALWAYS TRIGGER trigger_name
      19. | DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
      20. | DISABLE ROW LEVEL SECURITY
      21. | ENABLE ROW LEVEL SECURITY
      22. | FORCE ROW LEVEL SECURITY
      23. | NO FORCE ROW LEVEL SECURITY
      24. | ENCRYPTION KEY ROTATION
      25. | INHERIT parents
      26. | NO INHERIT parents
      27. | OF type_name
      28. | NOT OF
      29. | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
      30. | AUTO_INCREMENT [ = ] value
      31. | COMMENT {=| } 'text'
      32. | ALTER INDEX index_name [ VISBLE | INVISIBLE ]
      33. | [ [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]
      34. | CONVERT TO CHARACTER SET | CHARSET charset | DEFAULT [ COLLATE collation ]
      35. | MODIFY column_name column_type ON UPDATE CURRENT_TIMESTAMP

    其中列相关的操作column_clause可以是以下子句之一:

    1. ADD [ COLUMN ] column_name data_type [ CHARACTER SET | CHARSET [ = ] charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ] [ FIRST | AFTER column_name ]
    2. | MODIFY column_name data_type
    3. | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
    4. | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
    5. | MODIFY [ COLUMN ] column_name data_type [ CHARACTER SET | CHARSET [ = ] charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
    6. | ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    7. | ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }
    8. | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    9. | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer
    10. | ADD STATISTICS (( column_1_name, column_2_name [, ...] ))
    11. | DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))
    12. | ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )
    13. | ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    14. | ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    • ADD [ COLUMN ] column_name data_type [ CHARACTER SET | CHARSET [ = ] charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ … ] ] [ FIRST | AFTER column_name]

      向表中增加一个新的字段。用ADD COLUMN增加一个字段,所有表中现有行都初始化为该字段的缺省值(如果没有声明DEFAULT子句,值为NULL)。其中FIRST | AFTER column_name表示新增字段到某个位置。

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

      向表中增加多列。

    • MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, …] )

      修改表已存在字段的数据类型。

    • MODIFY [ COLUMN ] column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ … ] ] [FIRST | AFTER column_name] 修改表已存在字段的定义,将用新定义替换字段原定义,原字段上的索引、独立对象约束(例如:主键、唯一键、CHECK约束等)不会被删除。[FIRST | AFTER column_name]语法表示修改字段定义的同时修改字段在表中的位置。 此语法只能在参数sql_compatibility=’B’时使用。不支持列存表,不支持外表,不支持修改加密字段,不支持修改分区键字段的数据类型和排序规则,不支持修改规则引用的字段的数据类型和排序规则,不支持修改物化视图引用的字段的数据类型和排序规则。 被修改数据类型或排序规则的字段如果被一个生成列引用,这个生成列的数据将会重新生成。 被修改字段若被一些对象依赖(比如:索引、独立对象约束、视图、触发器、行级访问控制策略等),修改字段过程中将会重建这些对象。若被修改后字段定义违反此类对象的约束,修改操作会失败,比如:修改作为视图结果列的字段的数据类型。请修改字段前评估这类影响。 被修改字段若被一些对象调用(比如:自定义函数、存储过程等),修改字段不会处理这些对象。修改字段完毕后,这些对象有可能出现不可用的情况,请修改字段前评估这类影响。 修改字段的字符集或字符序会将字段中的数据转换为新的字符集进行编码。 此子句与上一子句中“MODIFY column_name data_type”部分语法相同,语义功能不同,当GUC参数b_format_behavior_compat_options含有’enable_modify_column’选项时,将按照此子句功能处理。

    • CHANGE [ COLUMN ] old_column_name new_column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ … ] ] [FIRST | AFTER column_name] 修改表已存在字段的名称和定义,字段新名称不能是已有字段的名称,将用新名称和定义替换字段原名称和定义原字段上的索引、独立对象约束(例如:主键、唯一键、CHECK约束)等不会被删除。[FIRST | AFTER column_name]语法表示修改字段名称和定义的同时修改字段在表中的位置。 此语法只能在参数sql_compatibility=’B’时使用。不支持列存表,不支持外表。不支持修改加密字段,不支持修改分区键字段的数据类型和排序规则,不支持修改规则引用的字段的数据类型和排序规则,不支持修改物化视图引用的字段的数据类型和排序规则 被修改数据类型或排序规则的字段如果被一个生成列引用,这个生成列的数据将会重新生成。 被修改字段若被一些对象依赖(比如:索引、独立对象约束、视图、触发器、行级访问控制策略等),修改字段过程中将会重建这些对象。若被修改后字段定义违反此类对象的约束,修改操作会失败,比如:修改作为视图结果列的字段的数据类型。请修改字段前评估这类影响。 被修改字段若被一些对象调用(比如:自定义函数、存储过程等),修改字段不会处理这些对象。修改字段名称后,这些对象有可能出现不可用的情况,请修改字段前评估这类影响。 修改字段的字符集或字符序会将字段中的数据转换为新的字符集进行编码。

    • 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 | DELETE} STATISTICS ((column_1_name, column_2_name [, …]))

      用于添加和删除多列统计信息声明(不实际进行多列统计信息收集),以便在后续进行全表或全库analyze时进行多列统计信息收集。如果关闭GUC参数enable_functional_dependency,每组多列统计信息最多支持32列;如果开启GUC参数enable_functional_dependency,每组多列统计信息最多支持4列。不支持添加/删除多列统计信息声明的表:系统表、外表。

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

      设置/重置属性选项。 目前,属性选项只定义了n_distinct和n_distinct_inherited。n_distinct影响表本身的统计值,而n_distinct_inherited影响表及其继承子表的统计。目前,只支持SET/RESET n_distinct参数,禁止SET/RESET n_distinct_inherited参数。

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

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

    • 其中列约束column_constraint为:

      1. [ CONSTRAINT constraint_name ]
      2. { NOT NULL |
      3. NULL |
      4. CHECK ( expression ) |
      5. DEFAULT default_expr |
      6. GENERATED ALWAYS AS ( generation_expr ) [STORED] |
      7. AUTO_INCREMENT |
      8. ON UPDATE update_expr |
      9. UNIQUE [KEY] index_parameters |
      10. PRIMARY KEY index_parameters |
      11. ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
      12. REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
      13. [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
      14. [ COMMENT 'text' ]
    • 其中列的压缩可选项compress_mode为:

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

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

    • 其中表约束table_constraint为:

      1. [ CONSTRAINT [ constraint_name ] ]
      2. { CHECK ( expression ) |
      3. UNIQUE [ idx_name ] [ USING method ] ( { { column_name | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] |
      4. PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] }[, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] |
      5. PARTIAL CLUSTER KEY ( column_name [, ... ] ) |
      6. FOREIGN KEY [ idx_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
      7. [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
      8. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    • 其中索引参数index_parameters为:

      1. [ WITH ( {storage_parameter = value} [, ... ] ) ]
      2. [ USING INDEX TABLESPACE tablespace_name ]
    • 重命名表。对名称的修改不会影响所存储的数据。

      1. ALTER TABLE [ IF EXISTS ] table_name
      2. RENAME TO new_table_name;
    • 重命名表中指定的列。

      1. ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
      2. RENAME [ COLUMN ] column_name TO new_column_name;
    • 设置表的所属模式。

      1. ALTER TABLE [ IF EXISTS ] table_name

      说明:

      • 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列,需要将序列删除,重建,或者取消拥有关系, 才能将表schema更改成功。

      • 要修改一个表的模式,用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表,用户必须同时又是父表的所有者。要修改所有者,用户还必须是新的所有角色的直接或间接成员,并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能做出了重建和删除表之外的事情。不过,系统管理员可以以任何方式修改任意表的所有权限。

      • 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如,可以在一个命令里增加几个字段或修改几个字段的类型。对于大表,此种操作带来的效率提升更明显,原因在于只需要对该大表做一次处理。

      • 增加一个CHECK或NOT NULL约束将会扫描该表,以保证现有的行符合约束要求。

      • 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说,这个操作可能会花很长时间,并且它还临时需要两倍的磁盘空间。

    • 添加多个列。

      1. ALTER TABLE [ IF EXISTS ] table_name
      2. ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
    • 更新多个列。

      1. ALTER TABLE [ IF EXISTS ] table_name
      2. MODIFY ( { column_name data_type [ CHARACTER SET | CHARSET charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name] | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
    • 对表timestamp列添加ON UPDATE属性。

      1. ALTER TABLE table_name
      2. MODIFY column_name column_type ON UPDATE CURRENT_TIMESTAMP;
    • 对表timestamp列删除ON UPDATE属性。

    参数说明

    • IF EXISTS

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

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

      table_name是需要修改的表名。

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

    • constraint_name

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

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

        ALTER TABLE - 图2 须知:

        对于新增约束,在B模式数据库下(即sql_compatibility = ‘B’)constraint_name为可选项,在其他模式数据库下,必须加上constraint_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指定按降序排序。

      ALTER TABLE - 图5 须知:

      在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_namecolumn_1_name、 column_2_name

      现存的或新字段的名称。

    • data_type

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

    • compress_mode

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

    • 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’)下支持的字符集和字符序介绍)。

      ALTER TABLE - 图7 说明:

    • USING expression

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

      说明:

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

    • NOT NULL | NULL

      设置列是否允许空值。

    • 带符号的整数常值。当使用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表示像普通列一样存储生成列的值。

      ALTER TABLE - 图9 说明:

      • 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 [, … ] ) index_parameters

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

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

    • 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:只有在事务结尾才检查它。

        说明: Ustore表不支持新增DEFERRABLE 以及 INITIALLY DEFERRED约束。

    • PARTIAL CLUSTER KEY

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

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

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

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

    • schema_name

      表所在的模式名称。

    • VISIBLE | INVISIBLE

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

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

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

    • [DEFAULT] COLLATE [ = ] default_collation

      仅在sql_compatibility=’B’时支持该语法。修改表的默认字符序,单独指定时会将表的默认字符集设置为指定的字符序对应的字符集。字符序参见。

      ALTER TABLE - 图12 说明: 未显式指定表的字符集或字符序时,若指定了模式的默认字符集或字符序,表字符集和字符序将从模式上继承。若模式的默认字符集或字符序不存在,当b_format_behavior_compat_options = ‘default_collation’时,表的字符集和字符序将继承当前数据库的字符集及其对应的默认字符序。

    请参考CREATE TABLE的。

    1. -- 创建B模式数据库。
    2. openGauss=# create database test_first_after dbcompatibility 'b';
    3. openGauss=# \c test_first_after
    4. -- 创建表t1并插入数据。
    5. openGauss=# drop table if exists t1 cascade;
    6. openGauss=# create table t1(f1 int, f2 varchar(20), f3 timestamp, f4 bit(8), f5 bool);
    7. openGauss=# insert into t1 values(1, 'a', '2022-11-08 19:56:10.158564', x'41', true), (2, 'b', '2022-11-09 19:56:10.158564', x'42', false);
    8. -- 指定位置新增字段
    9. openGauss=# alter table t1 add f6 clob first;
    10. openGauss=# alter table t1 add f7 blob after f2;
    11. openGauss=# alter table t1 add f8 int, add f9 text first, add f10 float after f3;
    12. -- 查询t1表结构
    13. openGauss=# \d+ t1
    14. -- 查询t1表数据
    15. openGauss=# select * from t1;
    16. -- 修改字段到指定位置
    17. openGauss=# alter table t1 modify f3 timestamp first;
    18. openGauss=# alter table t1 modify f1 int after f5;
    19. -- 查询t1表结构
    20. openGauss=# \d+ t1
    21. -- 查询t1表数据
    22. openGauss=# select * from t1;
    23. -- 修改t1表的默认字符集为utf8mb4,默认字符序为utf8mb4_bin
    24. openGauss=# alter table t1 charset utf8mb4 collate utf8mb4_bin;
    25. -- t1表中字符类型字段的数据转化为utf8mb4编码,并设置表和字段的字符序为utf8mb4_bin
    26. openGauss=# alter table t1 convert to charset utf8mb4 collate utf8mb4_bin;
    27. -- t1表新增字段并设置字段的字符集为utf8mb4,字符序为utf8mb4_bin
    28. openGauss=# alter table t1 add t10 varchar(20) charset utf8mb4 collate utf8mb4_bin;
    29. -- 修改t1表的t10字段的字符集为utf8mb4,字符序为utf8mb4_unicode_ci
    30. openGauss=# alter table t1 modify t10 varchar(20) charset utf8mb4 collate utf8mb4_unicode_ci;
    31. -- 创建INVISIBLE唯一索引
    32. openGauss=# alter table t1 add constraint uniq_a unique (f1) invisible;
    33. -- 修改索引为VISIBLE

    相关链接

    DROP TABLE