ALTER TABLE#

ALTER TABLE

ALTER TABLE — 更改一个表的定义

大纲

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE [ONLY] <name> SET
    DISTRIBUTED BY (<column>, [ ... ] )
    | DISTRIBUTED RANDOMLY
    | WITH (REORGANIZE=true|false)

其中action 是以下之一：

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    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
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
    ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
    ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    DISABLE ROW LEVEL SECURITY
    ENABLE ROW LEVEL SECURITY
    FORCE ROW LEVEL SECURITY
    NO FORCE ROW LEVEL SECURITY
    SET WITHOUT OIDS
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE |
  PRIMARY KEY }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

而table_constraint是：

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) |
  PRIMARY KEY ( column_name [, ... ] ) }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

并且 table_constraint_using_index 是：

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

描述

ALTER TABLE更改一个现有表的定义。下文描述了几种形式。注意每一种形式所要求的锁级别可能不同。如果没有明确说明，将会持有一个ACCESS EXCLUSIVE所。当列出多个子命令时，所持有的锁将是子命令所要求的最严格的那一个。

ADD COLUMN [ IF NOT EXISTS ]

这种形式向该表增加一个新列，使用与 CREATE TABLE相同的语法。如果指定了 IF NOT EXISTS并且使用这个名字的列已经存在，则不会抛出错误。

DROP COLUMN [ IF EXISTS ]

这种形式从表删除一列。涉及到该列的索引和表约束也将会被自动删除。如果该列的移除会导致引用它的多元统计信息仅包含单一列的数据，则该多元统计信息也将被移除。如果在该表之外有任何东西（例如视图）依赖于该列，你将需要用到CASCADE。如果指定了 IF EXISTS但该列不存在，则不会抛出错误。这种情况中会发出一个提示。

SET DATA TYPE

这种形式更改表中一列的类型。涉及到该列的索引和简单表约束将通过重新解析最初提供的表达式被自动转换为使用新的列类型。可选的 COLLATE子句为新列指定一种排序规则，如果被省略，排序规则会是新列类型的默认排序规则。可选的USING 子句指定如何从旧的列值计算新列值，如果被省略，默认的转换和从旧类型到新类型的赋值造型一样。如果没有从旧类型到新类型的隐式或者赋值造型，则必须提供一个USING子句。

SET/DROP DEFAULT

这些形式为一列设置或者移除默认值。默认值只在后续的 INSERT或UPDATE命令中生效，它们不会导致已经在表中的行改变。

SET/DROP NOT NULL

这些形式更改一列是否被标记为允许空值或者拒绝空值。

SET NOT NULL 只能应用于列，前提是表中没有任何记录包含该列的NULL值。通常，这一点在ALTER TABLE全表扫描时来检查；但是，如果找到有效的CHECK约束证明不存在NULL，则跳过表扫描。

如果这个表是一个分区，对于在父表中被标记为NOT NULL的列，不能在其上执行DROP NOT NULL。要从所有的分区中删除NOT NULL约束，可以在父表上执行DROP NOT NULL。即使在父表上没有NOT NULL约束，这样的约束还是能被增加到分区上。也就是说，即便父表允许空值，子表也可以不允许空值，但反过来不行。

ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY SET GENERATED { ALWAYS | BY DEFAULT } DROP IDENTITY [ IF EXISTS ]

这些形式更改一列是否是一个标识列，或者是更改一个已有的标识列的产生属性。详情请参考CREATE TABLE。

如果DROP IDENTITY IF EXISTS被指定并且该列不是一个标识列，则不会有错误被抛出。在这种情况下会发出一个提示。

SET sequence_option RESTART

这些形式修改位于一个现有标识列之下的序列。``sequence_option``是一个ALTER SEQUENCE所支持的选项，例如INCREMENT BY。

SET STATISTICS

这种形式为后续的ANALYZE操作设置针对每列的统计收集目标。目标可以被设置在范围 0 到 10000 之间，还可以把它设置为 -1 来恢复到使用系统默认的统计目标（ default_statistics_target）。 PostgreSQL查询规划器使用统计

SET STATISTICS要求一个SHARE UPDATE EXCLUSIVE锁。

ADD table_constraint [ NOT VALID ]

这种形式使用和CREATE TABLE相同的语法外加 NOT VALID选项为一个表增加一个新的约束，该选项当前只被允许用于 CHECK 约束。

通常，此窗体将导致对表进行扫描，以验证表中的所有现有行是否满足新约束。但是如果使用了 NOT VALID选项，则跳过此可能很漫长的扫描。该约束仍将被强制到后续的插入和删除上（也就是说，如果新行不匹配指定的检查条件，操作会失败）。但是数据库不会假定约束对该表中的所有行都成立，直到通过使用VALIDATE CONSTRAINT选项对它进行验证。参见下述 Notes 以了解关于使用NOT VALID选项的更多信息。

当唯一或者主键约束被添加到分区表时，会有额外的限制，请参考CREATE TABLE。

ADD table_constraint_using_index

这种形式基于一个已有的唯一索引为一个表增加新的 PRIMARY KEY或UNIQUE约束。该索引中的所有列将被包括在约束中。

该索引不能有表达式列或者是一个部分索引。还有，它必须是一个带有默认排序顺序的 B-树索引。这些限制确保该索引等效于使用常规 ADD PRIMARY KEY或者ADD UNIQUE命令时创建的索引。

如果PRIMARY KEY被指定，并且该索引的列没有被标记 NOT NULL，那么这个命令将尝试对每一个这样的列做 ALTER COLUMN SET NOT NULL。这需要一次全表扫描来验证这些列不包含空值。在所有其他情况中，这都是一种很快的操作。

如果提供了一个约束名，那么该索引将被重命名以匹配该约束名。否则该约束将被命名成索引的名称。

这个命令被执行后，该索引被增加的约束“拥有”，这和用常规 ADD PRIMARY KEY或ADD UNIQUE命令创建的索引一样。特别地，删掉该约束将会导致该索引也消失。

当前在分区表上不支持这种形式。

备注

注意

如果需要增加一个新的约束但是不希望长时间阻塞表更新，那么使用现有索引增加约束会有所帮助。要这样做，用 CREATE INDEX CONCURRENTLY创建该索引，并且接着使用这种语法把它安装为一个正式的约束。例子见下文。

VALIDATE CONSTRAINT

这种形式验证之前创建为NOT VALID的检查约束，它会扫描表来确保对于该约束没有行不满足约束。如果约束已经被标记为合法，则什么也不会发生。 (参见下述 Notes 以了解此命令用途的说明。)

DROP CONSTRAINT [ IF EXISTS ]

这种形式在一个表上删除指定的约束，还有位于该约束之下的任何索引。如果IF EXISTS 被指定并且该约束不存在，不会抛出错误。在这种情况下会发出一个提示。

DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE

这些形式配置属于表的重写规则的触发设置。系统仍然知道一个被禁用规则的存在，但在查询重写时不会应用它。其语义与禁用的/启用的触发器的一样。对于ON SELECT规则会忽略这个配置，即使当前会话处于一种非默认的复制角色，这类规则总是会被应用以保持视图工作正常。

DISABLE/ENABLE ROW LEVEL SECURITY

这些形式控制属于该表的行安全性策略的应用。如果被启用并且该表上不存在策略，则将应用一个默认否定的策略。注意即使行级安全性被禁用，在表上还是可以存在策略。在这种情况下，这些策略将不会被应用并且会被忽略。另见CREATE POLICY。

NO FORCE/FORCE ROW LEVEL SECURITY

这些形式控制当用户是表拥有者时表上的行安全性策略的应用。如果被启用，当用户是表拥有者时，行级安全性策略将被应用。如果被禁用（默认），则当用户是表拥有者时，行级安全性将不会被应用。另见 CREATE POLICY。

SET WITHOUT OIDS

向后兼容的语法，用于删除oid系统列。由于oid系统列无法再添加，所以不会有实际效果。

INHERIT parent_table

这种形式把目标表增加为指定父表的一个新子女。随后，针对父亲的查询将包括目标表中的记录。要被增加为一个子女，目标表必须已经包含和父表完全相同的列（也可以有额外的列）。这些列必须具有匹配的数据类型，并且如果它们在父表中具有NOT NULL约束，它们在子表中也必须有NOT NULL约束。

也必须把子表约束与所有父表的CHECK约束进行匹配，不过父表中那些被标记为非可继承（也就是用ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT 创建的）除外，它们会被忽略。所有匹配得上的子表约束不能被标记为不可继承。当前，UNIQUE以及PRIMARY KEY约束没有被考虑，但是这种情况可能会在未来发生变化。

NO INHERIT parent_table

这种形式把目标表从指定父表的子女列表中移除。针对父表的查询将不再包括来自目标表的记录。

OF type_name

这种形式把该表链接到一种组合类型，就好像CREATE TABLE OF所做的那样。该表的列名和类型列表必须精确地匹配该组合类型。该表必须不从任何其他表继承。这些限制确保CREATE TABLE OF能允许一个等价的表定义。

NOT OF

这种形式解除一个有类型的表和其类型之间的关联。

OWNER TO

这种形式把表、序列、视图、物化视图或外部表的拥有者改为指定用户。

DISTRIBUTED BY

这种形式指定一个表的分布策略。更改分布策略将导致表数据在磁盘上物理重新分布，这可能会消耗大量资源。如果您声明相同的分布策略或从哈希更改为随机分布，除非您声明set with (REORGANIZE=true),否则数据不会被重新分布。

magma表不支持随机分布。

REPLICA IDENTITY

这种形式更改被写入到预写式日志来标识被更新或删除行的信息。除非使用逻辑复制，这个选项将不会产生效果。DEFAULT（非系统表的默认值）记录主键列（如果有）的旧值。USING INDEX记录被所提到的索引所覆盖的列的旧值，该索引必须是唯一索引、不是部分索引、不是可延迟索引并且只包括被标记成 NOT NULL的列。FULL记录行中所有列的旧值。 NOTHING不记录有关旧行的任何信息（这是系统表的默认值）。在所有情况下，除非至少有一个要被记录的列在新旧行版本之间发生变化，将不记录旧值。

RENAME

RENAME形式更改一个表（或者一个索引、序列、视图、物化视图或者外部表）的名称、表中一个列的名称或者表的一个约束的名称。在重命名一个具有底层索引的约束时，该索引也会被重命名。它对已存储的数据没有影响。

SET SCHEMA

这种形式把该表移动到另一个模式中。相关的该表列拥有的索引、约束和序列也会被移动。

除了RENAME和SET SCHEMA之外，所有形式的ALTER TABLE都作用在单个表上，前面这些形式可以被组合成一个多修改的列表被一起应用。例如，可以在一个命令中增加多个列并且/或者修改多个列的类型。对于大型表来说这会特别有用，因为只需要对表做一趟操作。

要使用ALTER TABLE，你必须拥有该表。要更改一个表的模式或者表空间，你还必须拥有新模式或表空间上的 CREATE特权。要把一个表作为一个父表的新子表加入，你必须也拥有该父表。此外，要把一个表挂接为另一个表的新分区，你必须拥有被挂接的表。要更改拥有者，你还必须是新拥有角色的一个直接或者间接成员，并且该角色必须具有该表的模式上的 CREATE特权（这些限制强制修改拥有者不能做一些通过删除和重建表做不到的事情。不过，一个超级用户怎么都能更改任何表的所有权。）。要增加一个列、修改一列的类型或者使用OF子句，你还必须具有该数据类型上的USAGE特权。

参数

IF EXISTS: 如果表不存在则不要抛出一个错误。这种情况下会发出一个提示。
``name``: 要修改的一个现有表的名称（可以是模式限定的）。如果在表名前指定了 ONLY，则只会修改该表。如果没有指定ONLY，该表及其所有后代表（如果有）都会被修改。可选地，在表名后面可以指定 *用来显式地指示包括后代表。
``column_name``: 一个新列或者现有列的名称。
``new_column_name``: 一个现有列的新名称。
``new_name``: 该表的新名称。
``data_type``: 一个新列的数据类型或者一个现有列的新数据类型。
``table_constraint``: 该表的新的表约束。
``constraint_name``: 一个新约束或者现有约束的名称。
CASCADE: 自动删除依赖于被删除列或约束的对象（例如引用该列的视图），并且接着删除依赖于那些对象的所有对象（见依赖跟踪）。
RESTRICT: 如果有任何依赖对象时拒绝删除列或者约束。这是默认行为。
``index_name``: 一个现有索引的名称。
``parent_table``: 要与这个表关联或者解除关联的父表。
``new_owner``: 该表的新拥有者的用户名。
``new_schema``: 要把该表移入其中的模式的名称。

Notes

关键词COLUMN是噪声，可以被省略。

在使用ADD COLUMN增加一列并且指定了一个非易失性DEFAULT时，默认值会在该语句执行时计算并且结果会被保存在表的元数据中。这个值将被用于所有现有行的该列。如果没有指定DEFAULT，则使用NULL。在两种情况下都不需要重写表。

增加一个带有非易失性DEFAULT子句的列或者更改一个现有列的类型将要求重写整个表及其索引。在更改一个现有列的类型时有一种例外：如果 USING子句不更改列的内容并且旧类型在二进制上与新类型可强制转换或者是新类型上的一个未约束域，则不需要重写表。但是受影响列上的任何索引仍必须被重建。对于一个大型表，表和/或索引重建可能会消耗相当多的时间，并且会临时要求差不多两倍的磁盘空间。

增加一个CHECK或者NOT NULL约束要求扫描表以验证现有行符合该约束，但是不要求一次表重写。

提供在一个ALTER TABLE中指定多个更改的选项的主要原因就是多次表扫描或者重写可以因此被整合成一次。

扫描大型表以验证新的检查约束可能需要很长时间，并且对表的其他更新将锁定，直到ALTER TABLE ADD CONSTRAINT命令被提交。 NOT VALID约束选项的主要目的是减少对并发更新添加约束的影响。使用 NOT VALID，ADD CONSTRAINT命令不扫描表，可以立即提交。在之后，VALIDATE CONSTRAINT命令会被发出以验证现有行是否满足约束。验证步骤不需要锁定并发更新，因为它知道其他事务将强制执行它们插入或更新的行的约束;只有预先存在的行需要检查。因此，验证在被更改的表上仅获得一个SHARE UPDATE EXCLUSIVE锁。除了改进并发性外，在已知该表包含预先存在的违规行为的情况下使用 NOT VALID 和 VALIDATE CONSTRAINT也能有作用。一旦约束就位，就不能再插入新的违规，，并且现有问题可以在空闲时纠正，直到VALIDATE CONSTRAINT最终完成。

DROP COLUMN形式不会在物理上移除列，而只是简单地让它对 SQL 操作不可见。后续该表中的插入和更新操作将为该列存储一个空值。因此，删除一个列很快，但是它不会立刻减少表所占的磁盘空间，因为被删除列所占用的空间还没有被回收。随着现有列被更新，空间将被逐渐回收。

要强制立即回收被已删除列占据的空间，你可以执行一种能导致全表重写的 ALTER TABLE形式。这种形式会导致重新构造每一个把被删除列替换为空值的行。

ALTER TABLE的重写形式对于 MVCC 是不安全的。在一次表重写之后，如果并发事务使用的是一个在重写发生前取得的快照，该表将对这些并发事务呈现出空表的形态。详见提醒。

SET DATA TYPE的USING选项能实际指定涉及该列旧值的任何表达式。也就是说，它可以不但可以引用要被转换的列，还可以引用其他列。这允许使用SET DATA TYPE语法完成十分普遍的转换。由于这种灵活性，USING表达式不适合于列的默认值（如果有），结果可能不是一个默认值所需的常量表达式。这意味着在没有从旧类型到新类型的隐式或者赋值造型时，即便提供了一个 USING子句，SET DATA TYPE还是可能无法转换默认值。在这种情况下，用DROP DEFAULT删除该默认值，执行ALTER TYPE并且接着使用SET DEFAULT增加一个合适的新默认值。类似的考虑也适用于涉及该列的索引和约束。

如果一个表有任何后代表，在不对后代表做相同操作的情况下，不允许在父表中增加列、重命名列或者更改列的类型。这确保了后代总是具有和父表匹配的列。类似地，如果不对所有后代上的CHECK约束进行重命名，就不能在父表中重命名该CHECK约束，这样CHECK约束也能在父表及其后代之间保持匹配（不过，这个限制不适用于基于索引的约束）。此外，因为从父表中选择也会从其后代中选择，父表上的约束不能被标记为有效，除非它在那些后代上也被标记为有效。在所有这些情况下，ALTER TABLE ONLY都将被拒绝。

只有当一个后代表的列不是从任何其他父表继承而来并且没有该列的独立定义时，一次递归的DROP COLUMN操作才会移除该列。一次非递归的DROP COLUMN（即 ALTER TABLE ONLY ... DROP COLUMN）不会移除任何后代列，而是会把它们标记成独立定义的列。对于一个分区表，一个非递归的DROP COLUMN命令将会失败，因为一个表的所有分区都必须有和分区根节点相同的列。

标识列的动作（ADD GENERATED、SET等、DROP IDENTITY）以及动作OWNER不会递归到后代表上，也就是说它们执行时总是好像指定了ONLY一样。增加约束的动作仅对没有标记为NO INHERIT的CHECK约束递归。

不允许更改一个系统目录表的任何部分。

可用参数的进一步描述请见CREATE TABLE。

示例

要向一个表增加一个类型为varchar的列：

ALTER TABLE distributors ADD COLUMN address varchar(30);

要从表中删除一列：

ALTER TABLE distributors DROP COLUMN address RESTRICT;

要在一个操作中更改两个现有列的类型：

ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);

通过一个USING子句更改一个包含 Unix 时间戳的整数列为 timestamp with time zone：

ALTER TABLE foo
    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

同样的，当该列具有一个不能自动造型成新数据类型的默认值表达式时：

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

To rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

重命名一个现有的表：

ALTER TABLE distributors RENAME TO suppliers;

重命名一个现有的约束：

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

为一列增加一个非空约束：

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

从一列移除一个非空约束：

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

向一个表及其所有子女增加一个检查约束：

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

只向一个表增加一个检查约束（不为其子女增加）：

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

（该检查约束也不会被未来的子女继承）。

从一个表及其子女移除一个检查约束：

ALTER TABLE distributors DROP CONSTRAINT zipchk;

只从一个表移除一个检查约束：

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

（该检查约束仍为子女表保留在某个地方）。

为一个表增加一个（多列）唯一约束：

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

为一个表增加一个自动命名的主键约束，注意一个表只能拥有一个主键：

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

把一个表移动到一个不同的模式：

兼容性

形式ADD（没有USING INDEX）、 DROP [COLUMN]、DROP IDENTITY、RESTART、 SET DEFAULT、SET DATA TYPE（没有USING)、 SET GENERATED以及SET sequence_option服从SQL标准。其他形式都是OushuDB对SQL标准的扩展。此外，在单个ALTER TABLE命令中指定多个操作的能力是一种扩展。

另见

CREATE TABLE

OushuDB 产品文档

ALTER TABLE

ALTER TABLE#