PostgreSQL 中文参考文档
🌐 英文原版 官方 ↗
ALTER TABLE
上一页 上一级SQL 命令首页 下一页

ALTER TABLE

ALTER TABLE — 更改一个表的定义

Synopsis

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 ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

where action is one of:

    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 SET EXPRESSION AS ( expression )
    ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
    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 | DEFAULT }
    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 | DEFAULT }
    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
    ALTER CONSTRAINT constraint_name [ INHERIT | NO INHERIT ]
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    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
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ACCESS METHOD { new_access_method | DEFAULT }
    SET TABLESPACE new_tablespace
    SET { LOGGED | UNLOGGED }
    SET ( storage_parameter [= value] [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL [ NO INHERIT ] |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  NOT NULL column_name [ NO INHERIT ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ]  [, PERIOD refcolumn ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]

and table_constraint_using_index is:

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

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

描述

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 子句。

使用这种形式时，该列的统计信息会被移除，因此建议之后在该表上运行 ANALYZE。对于虚拟生成列，ANALYZE 是不必要的，因为这类列从来没有统计信息。

SET/DROP DEFAULT #

这些形式设置或移除一个列的默认值（其中移除等价于把默认值设置为 NULL）。新的默认值只会在后续的 INSERT 或 UPDATE 命令中生效；它不会导致表中已经存在的行发生改变。

SET/DROP NOT NULL #

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

SET NOT NULL 只有在该表中没有任何记录包含该列的 NULL 值时才能应用于该列。通常这会在 ALTER TABLE 期间通过扫描整个表来检查，除非指定了 NOT VALID；但是，如果存在一个有效的 CHECK 约束（并且没有在同一命令中被删除）能够证明不可能存在 NULL，那么就会跳过表扫描。如果一个列有一个无效的非空约束，SET NOT NULL 会验证它。

如果这个表是一个分区，就不能对某个列执行 DROP NOT NULL，前提是该列在父表中被标记为 NOT NULL。要删除所有分区上的 NOT NULL 约束，应在父表上执行 DROP NOT NULL。即使父表上没有 NOT NULL 约束，如果需要，这样的约束仍然可以被添加到单个分区上；也就是说，即使父表允许空值，子表也可以拒绝空值，但反过来则不行。也可以仅将 NOT NULL 约束从 ONLY 父表中删除，这不会把它从子表中移除。

SET EXPRESSION AS #

这种形式替换一个生成列的表达式。存储生成列中的现有数据会被重写，并且未来的所有更改都将应用新的生成表达式。

当这种形式用于存储生成列时，其统计信息会被移除，因此建议之后在该表上运行 ANALYZE。对于虚拟生成列，ANALYZE 是不必要的，因为这类列从来没有统计信息。

DROP EXPRESSION [ IF EXISTS ] #

这种形式把一个存储生成列转换成普通的基础列。列中的现有数据会被保留，但未来的更改将不再应用生成表达式。

这种形式目前只支持存储生成列（不支持虚拟生成列）。

如果指定了 DROP EXPRESSION IF EXISTS 并且该列不是生成列，则不会抛出错误。在这种情况下会发出一个提示。

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

这些形式更改一个列是否为标识列，或者更改现有标识列的生成属性。详见 CREATE TABLE。与 SET DEFAULT 一样，这些形式只影响后续 INSERT 和 UPDATE 命令的行为；它们不会导致表中已经存在的行发生改变。

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

SET sequence_option
RESTART #

这些形式修改作为现有标识列基础的序列。sequence_option 是 ALTER SEQUENCE 支持的一个选项，例如 INCREMENT BY。

SET STATISTICS #

这种形式为后续的 ANALYZE 操作设置针对每个列收集统计信息的目标。该目标可以设置为 0 到 10000 范围内的值。把它设置为 DEFAULT 可以恢复为使用系统默认的统计信息目标（default_statistics_target）。（设置为 -1 是得到相同结果的一种过时写法。）有关 PostgreSQL 查询规划器如何使用统计信息的更多信息，请参考 Section 14.2。

SET STATISTICS 会获取一个 SHARE UPDATE EXCLUSIVE 锁。

SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] ) #

这种形式设置或重置针对每个属性的选项。目前，唯一定义的针对每个属性的选项是 n_distinct 和 n_distinct_inherited，它们会覆盖后续 ANALYZE 操作所做的不同值数量的估计。n_distinct 影响表本身的统计信息，而 n_distinct_inherited 影响为表加上其继承子表收集的统计信息，以及为分区表收集的统计信息。当指定的值是一个正值时，查询规划器会假定该列正好包含指定数量的不同非空值。也可以通过使用小于 0 且大于等于 -1 的值来指定非整数值。这会指示查询规划器把指定数字的绝对值乘以表中估计的行数来估计不同值的数量。例如，值 -1 意味着该列中的所有值都互不相同，而值 -0.5 意味着每个值平均出现两次。当表的大小随时间变化时，这可能很有用。有关 PostgreSQL 查询规划器如何使用统计信息的更多信息，请参考 Section 14.2。

更改针对每个属性的选项会获取一个 SHARE UPDATE EXCLUSIVE 锁。

SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } #

这种形式设置一个列的存储模式。它控制该列是内联保存还是在次级 TOAST 表中保存，以及数据是否应该被压缩。PLAIN 必须用于像 integer 这样的定长值，它是内联的、未压缩的。MAIN 用于内联的、可压缩的数据。EXTERNAL 用于外部的、未压缩的数据，而 EXTENDED 用于外部的、已压缩的数据。写入 DEFAULT 会把存储模式设置为该列数据类型的默认模式。EXTENDED 是大多数支持非 PLAIN 存储的数据类型的默认值。使用 EXTERNAL 会让对超大 text 和 bytea 值的子串操作运行得更快，代价是存储空间增加。注意 ALTER TABLE ... SET STORAGE 本身并不会改变表中的任何东西；它只是设置在未来的表更新期间要采用的策略。更多信息请参见 Section 66.2。

SET COMPRESSION compression_method #

这种形式设置一个列的压缩方法，决定以后插入的值将如何被压缩（如果存储模式根本允许压缩的话）。这不会导致表被重写，因此现有数据可能仍然使用其他压缩方法压缩。如果该表用 pg_restore 恢复，那么所有值都会用配置的压缩方法重写。但是，当从另一个关系插入数据时（例如通过 INSERT ... SELECT），来自源表的值不一定会被解 TOAST，因此任何先前已压缩的数据可能保留其现有的压缩方法，而不会用目标列的压缩方法重新压缩。支持的压缩方法是 pglz 和 lz4。（lz4 只有在 --with-lz4 被用于构建 PostgreSQL 时才可用。）此外，compression_method 可以是 default，它选择默认行为，即在插入数据时参考 default_toast_compression 设置来确定要使用的方法。

ADD table_constraint [ NOT VALID ] #

这种形式使用与 CREATE TABLE 相同的约束语法向表添加一个新约束，外加 NOT VALID 选项，该选项目前只允许用于外键约束、CHECK 约束和非空约束。

通常，这种形式会导致对表进行一次扫描，以验证表中所有已存在的行都满足新约束。但是如果使用了 NOT VALID 选项，这个可能很耗时的扫描就会被跳过。该约束仍将针对后续的插入或更新被应用（也就是说，就外键而言，除非被引用表中存在匹配的行，否则它们将失败；除非新行满足指定的检查条件，否则它们也将失败）。但是在使用 VALIDATE CONSTRAINT 选项验证该约束之前，数据库不会假定该约束对表中的所有行都成立。参见 Notes 下文中关于使用 NOT VALID 选项的更多信息。

虽然 ADD table_constraint 的大多数形式都需要一个 ACCESS EXCLUSIVE 锁，但 ADD FOREIGN KEY 只需要一个 SHARE ROW EXCLUSIVE 锁。注意 ADD FOREIGN KEY 除了在声明约束的表上获取锁之外，还会在引用表上获取一个 SHARE ROW EXCLUSIVE 锁。

当向分区表添加唯一约束或主键约束时，还有一些额外的限制；参见 CREATE TABLE。

ADD table_constraint_using_index #

这种形式基于一个现有的唯一索引向表添加新的 PRIMARY KEY 或 UNIQUE 约束。索引的所有列都将被包含在该约束中。

该索引不能包含表达式列，也不能是部分索引。此外，它必须是一个采用默认排序顺序的 b-tree 索引。这些限制确保该索引与通过常规 ADD PRIMARY KEY 或 ADD UNIQUE 命令构建的索引等价。

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

如果提供了一个约束名称，那么该索引将被重命名为与该约束名称相同。否则，约束将被命名为与索引相同的名称。

在此命令执行之后，该索引将被该约束所“拥有”，就像该索引是通过常规 ADD PRIMARY KEY 或 ADD UNIQUE 命令构建的一样。特别地，删除该约束也会使该索引消失。

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

Note

在需要添加新约束而又不想长时间阻塞表更新的情况下，使用现有索引添加约束会很有帮助。为此，可以先使用 CREATE UNIQUE INDEX CONCURRENTLY 创建索引，然后使用这种语法把它转换为约束。参见下面的示例。

ALTER CONSTRAINT #

这种形式修改先前创建的约束的属性。目前只有外键约束可以用这种方式修改，但请参见下文。

ALTER CONSTRAINT ... INHERIT
ALTER CONSTRAINT ... NO INHERIT #

这些形式把一个可继承的约束修改为不可继承，或者反过来。目前只有非空约束可以用这种方式修改。除了更改约束的可继承状态之外，在把一个不可继承的约束标记为可继承的情况下，如果该表有子表，将为它们添加一个等价的约束。如果在有子表的表上把一个可继承的约束标记为不可继承，那么子表上相应的约束将被标记为不再是继承而来的，但不会被移除。

VALIDATE CONSTRAINT #

这种形式通过扫描表来验证先前以 NOT VALID 方式创建的外键、检查或非空约束，确保没有不满足该约束的行。如果该约束被设置为 NOT ENFORCED，则会抛出一个错误。如果该约束已被标记为有效，则什么也不会发生。（有关此命令用途的说明，请参见下文 Notes。）

该命令会获取一个 SHARE UPDATE EXCLUSIVE 锁。

DROP CONSTRAINT [ IF EXISTS ] #

这种形式删除表上指定的约束，以及该约束所基于的所有索引。如果指定了 IF EXISTS 并且该约束不存在，则不会抛出错误。在这种情况下会发出一个提示。

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER #

这些形式配置属于该表的触发器的触发行为。被禁用的触发器仍然为系统所知，但当其触发事件发生时不会被执行。（对于延迟触发器，启用状态是在事件发生时检查的，而不是在触发器函数实际执行时。）可以按名称禁用或启用单个触发器，或者禁用或启用表上的所有触发器，或者只禁用或启用用户触发器（此选项不包括内部生成的约束触发器，例如那些用于实现外键约束或可延迟的唯一约束和排他约束的触发器）。禁用或启用内部生成的约束触发器需要超级用户权限；执行此操作应当谨慎，因为如果触发器不执行，约束的完整性当然就无法保证。

触发器触发机制还受到配置变量 session_replication_role 的影响。仅被启用的触发器（默认情况）在复制角色为 “origin”（默认值）或 “local” 时会触发。配置为 ENABLE REPLICA 的触发器只有在会话处于 “replica” 模式时才会触发，而配置为 ENABLE ALWAYS 的触发器则无论当前复制角色是什么都会触发。

这种机制的效果是，在默认配置下，触发器不会在副本上触发。这很有用，因为如果一个触发器在源端用于在表之间传播数据，那么复制系统也会复制被传播的数据；因此该触发器不应在副本上再次触发，否则会导致重复。但是，如果触发器用于其他目的（例如创建外部告警），那么把它设置为 ENABLE ALWAYS 可能更合适，这样它在副本上也会触发。

当此命令应用于一个分区表时，除非指定了 ONLY，否则分区中相应克隆触发器的状态也会被更新。

该命令会获取一个 SHARE ROW EXCLUSIVE 锁。

DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE #

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

规则的触发机制也受到配置变量 session_replication_role 的影响，与上文所述的触发器类似。

DISABLE/ENABLE ROW LEVEL SECURITY #

这些形式控制属于该表的行安全策略的应用。如果启用并且该表没有任何策略，则会应用一个默认拒绝（default-deny）策略。注意，即使行级安全被禁用，表上也可能存在策略。在这种情况下，这些策略将不会被应用，而是被忽略。另请参见 CREATE POLICY。

NO FORCE/FORCE ROW LEVEL SECURITY #

这些形式控制当用户是表所有者时属于该表的行安全策略的应用。如果启用，当用户是表所有者时将应用行级安全策略。如果禁用（默认情况），当用户是表所有者时将不应用行级安全。另请参见 CREATE POLICY。

CLUSTER ON #

这种形式为将来的 CLUSTER 操作选择默认索引。它实际上并不会重新聚集该表。

更改聚集选项会获取一个 SHARE UPDATE EXCLUSIVE 锁。

SET WITHOUT CLUSTER #

这种形式从表中移除最近使用的 CLUSTER 索引指定。这会影响未来未指定索引的聚集操作。

更改聚集选项会获取一个 SHARE UPDATE EXCLUSIVE 锁。

SET WITHOUT OIDS #

用于移除 oid 系统列的向后兼容语法。由于 oid 系统列已不能再被添加，因此这种形式从不起作用。

SET ACCESS METHOD #

这种形式通过使用指定的访问方法重写表来更改表的访问方法；指定 DEFAULT 会选择被设置为 default_table_access_method 配置参数的访问方法。更多信息请参见 Chapter 62。

当应用于分区表时，没有数据需要重写，但之后创建的分区将默认使用给定的访问方法，除非被 USING 子句覆盖。指定 DEFAULT 会移除先前的值，使未来的分区默认使用 default_table_access_method。

SET TABLESPACE #

这种形式把表的表空间更改为指定的表空间，并将与该表关联的数据文件移动到新表空间。表上的索引（如果有）不会被移动，但可以用额外的 SET TABLESPACE 命令单独移动它们。当应用于分区表时，什么也不会移动，但之后用 CREATE TABLE PARTITION OF 创建的任何分区都将使用该表空间，除非被 TABLESPACE 子句覆盖。

通过使用 ALL IN TABLESPACE 形式，可以移动当前数据库中位于某个表空间内的所有表，这种形式会先锁定所有要移动的表，然后逐一移动它们。这种形式还支持 OWNED BY，它只移动由指定角色拥有的表。如果指定了 NOWAIT 选项，那么当命令无法立即获取所需的全部锁时就会失败。注意系统目录不会被此命令移动；如果需要，可以改用 ALTER DATABASE 或显式的 ALTER TABLE 调用。information_schema 关系不被视为系统目录的一部分，因此会被移动。另请参见 CREATE TABLESPACE。

SET { LOGGED | UNLOGGED } #

这种形式把表从非日志表更改为日志表，或者反过来（参见 UNLOGGED）。它不能应用于临时表。

这也会更改与该表关联的任何序列（用于标识列或 serial 列）的持久性。不过，也可以单独更改这些序列的持久性。

这种形式不支持分区表。

SET ( storage_parameter [= value] [, ... ] ) #

这种形式更改表的一个或多个存储参数。可用参数的详情请参见 Storage Parameters，其位于 CREATE TABLE 文档中。注意该表的内容不会立即被此命令修改；取决于参数的不同，你可能需要重写表才能获得期望的效果。这可以通过 VACUUM FULL、CLUSTER 或强制表重写的某种 ALTER TABLE 形式来完成。对于与规划器相关的参数，更改将从下一次锁定该表时开始生效，因此当前正在执行的查询不会受到影响。

将获取 SHARE UPDATE EXCLUSIVE 锁的情形包括：fillfactor、toast 和 autovacuum 存储参数，以及规划器参数 parallel_workers。

RESET ( storage_parameter [, ... ] ) #

这种形式把一个或多个存储参数重置为它们的默认值。与 SET 一样，可能需要重写表才能完全更新表。

INHERIT parent_table #

这种形式把目标表添加为指定父表的一个新子表。此后，针对父表的查询将包含目标表的记录。要作为子表被添加，目标表必须已经包含与父表完全相同的所有列（它也可以有额外的列）。这些列必须具有匹配的数据类型。

此外，父表上所有的 CHECK 和 NOT NULL 约束也必须存在于子表上，但被标记为不可继承的那些除外（即用 ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT 创建的约束），它们会被忽略。所有被匹配到的子表约束都不能被标记为不可继承。目前 UNIQUE、PRIMARY KEY 和 FOREIGN KEY 约束不会被考虑，但这种情况将来可能会改变。

NO INHERIT parent_table #

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

OF type_name #

这种形式把该表链接到一个复合类型，就像它是由 CREATE TABLE OF 创建的一样。该表的列名和类型列表必须与复合类型的完全匹配。该表不得继承自任何其他表。这些限制确保 CREATE TABLE OF 会允许一个等价的表定义。

NOT OF #

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

OWNER TO #

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

REPLICA IDENTITY #

这种形式更改写入预写式日志中、用于标识被更新或被删除行的信息。在大多数情况下，只有当某一列的旧值与新值不同时才会记录旧值；但是，如果旧值存储在外部，则无论它是否发生变化都会被记录。除非正在使用逻辑复制，否则该选项没有任何效果。

DEFAULT #

记录主键各列的旧值。这是非系统表的默认行为。当没有主键时，其行为与 NOTHING 相同。

USING INDEX index_name #

记录由命名索引所覆盖的列的旧值，该索引必须是唯一的、非部分的、不可延迟的，并且只包含被标记为 NOT NULL 的列。如果这个索引被删除，其行为与 NOTHING 相同。

FULL #

记录行中所有列的旧值。

NOTHING #

不记录关于旧行的任何信息。这是系统表的默认值。

RENAME #

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

SET SCHEMA #

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

ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } #

这种形式把一个现有的表（它本身也可以是被分区的表）附加为目标表的一个分区。可以使用 FOR VALUES 把该表附加为特定值的分区，或者使用 DEFAULT 把它附加为默认分区。对于目标表中的每一个索引，如果分区中已经存在一个有效的等价索引，那么该索引将被附加到目标表的索引上，就像执行过 ALTER INDEX ATTACH PARTITION 一样；否则，将创建一个新的相应索引。分区上的无效索引会被跳过。注意，如果现有表是一个外部表，那么当目标表上存在 UNIQUE 索引时，目前不允许把该表附加为目标表的分区。（另请参见 CREATE FOREIGN TABLE。）对于目标表中存在的每一个用户定义的行级触发器，都会在被附加的表中创建一个相应的触发器。

使用 FOR VALUES 的分区，其 partition_bound_spec 使用与 CREATE TABLE 相同的语法。分区边界规范必须与目标表的分区策略和分区键相对应。要被附加的表必须具有与目标表完全相同的所有列，且不能更多；此外，列类型也必须匹配。而且，它必须具有目标表的所有 NOT NULL 和 CHECK 约束，且这些约束没有被标记为 NO INHERIT。目前 FOREIGN KEY 约束不会被考虑。父表的 UNIQUE 和 PRIMARY KEY 约束如果尚不存在，将会在分区中被创建。

如果新分区是一个普通表，则会执行一次全表扫描，以检查表中已存在的行不违反分区约束。可以通过在运行此命令之前向表添加一个只允许满足所需分区约束的行的有效 CHECK 约束来避免这种扫描。该 CHECK 约束将被用来判定无需扫描表即可验证分区约束。但是，如果任何一个分区键是一个表达式并且该分区不接受 NULL 值，这种方法就不管用了。如果要附加一个不接受 NULL 值的列表分区，还应向分区键列添加一个 NOT NULL 约束，除非分区键是一个表达式。

如果新分区是一个外部表，则不会执行任何操作来验证外部表中的所有行都遵守分区约束。（有关外部表上约束的讨论，请参见 CREATE FOREIGN TABLE。）

当一个表拥有默认分区时，定义一个新分区会更改默认分区的分区约束。默认分区不能包含任何需要被移动到新分区的行，并且会被扫描以验证不存在这样的行。与新分区的扫描一样，如果存在合适的 CHECK 约束，这种扫描也可以避免。同样与新分区的扫描一样，当默认分区是一个外部表时，这种扫描总是会被跳过。

附加一个分区时，除了在被附加的表和默认分区（如果有）上获取 ACCESS EXCLUSIVE 锁之外，还会在父表上获取一个 SHARE UPDATE EXCLUSIVE 锁。

如果要被附加的表本身是一个分区表，那么还必须在所有子分区上持有额外的锁。如果默认分区本身是一个分区表，情况也一样。可以通过添加 CHECK 约束来避免对子分区的锁定，如 Section 5.12.2.2 中所述。

DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ] #

这种形式分离目标表的指定分区。被分离的分区将继续作为一个独立的表存在，但不再与它所脱离的表有任何关联。所有被附加到目标表索引上的索引都会被分离。所有作为目标表中触发器的克隆而创建的触发器都会被移除。对于在外键约束中引用此分区表的任何表，都会获取一个 SHARE 锁。

如果指定了 CONCURRENTLY，它将使用较低的锁级别运行，以避免阻塞可能正在访问分区表的其他会话。在这种模式下，内部会使用两个事务。在第一个事务期间，会在父表和分区上都获取一个 SHARE UPDATE EXCLUSIVE 锁，并且该分区被标记为正在分离；此时，该事务被提交，并等待所有其他正在使用该分区表的事务完成。一旦所有这些事务都已完成，第二个事务会在分区表上获取 SHARE UPDATE EXCLUSIVE 锁，并在分区上获取 ACCESS EXCLUSIVE 锁，然后分离过程完成。一个复制分区约束的 CHECK 约束会被添加到该分区上。CONCURRENTLY 不能在事务块中运行，并且如果分区表包含默认分区则不允许使用。

如果指定了 FINALIZE，之前被取消或中断的 DETACH CONCURRENTLY 调用将会被完成。一个分区表中同一时间最多只能有一个分区处于等待分离状态。

作用于单个表的所有 ALTER TABLE 形式，除了 RENAME、SET SCHEMA、ATTACH PARTITION 和 DETACH PARTITION 之外，都可以组合成一个将一起应用的多种更改的列表。例如，可以在单个命令中添加多个列和/或更改多个列的类型。这对于大表尤其有用，因为只需要对表进行一次遍历。

要使用 ALTER TABLE，你必须拥有该表。要更改表的模式或表空间，你还必须在新模式或表空间上具有 CREATE 特权。要把一个表添加为父表的新子表，你也必须拥有该父表。同样，要把一个表附加为该表的新分区，你必须拥有被附加的表。要更改所有者，你必须能够 SET ROLE 到新的拥有角色，并且该角色必须在该表的模式上具有 CREATE 特权。（这些限制强制确保更改所有者不会做任何你通过删除并重建表也无法做到的事情。不过，超级用户无论如何都可以更改任何表的所有者。）要添加列、更改列类型或使用 OF 子句，你还必须在该数据类型上具有 USAGE 特权。

参数

IF EXISTS #

如果表不存在则不抛出错误。在这种情况下会发出一个提示。

name #

要修改的现有表的名称（可以选择用模式限定）。如果在表名之前指定了 ONLY，则只修改该表。如果没有指定 ONLY，则修改该表及其所有后代表（如果有的话）。可以选择在表名之后指定 *，以显式指示包含后代表。

column_name #

新列或现有列的名称。

new_column_name #

现有列的新名称。

new_name #

表的新名称。

data_type #

新列的数据类型，或者现有列的新数据类型。

table_constraint #

该表的新表约束。

constraint_name #

新约束或现有约束的名称。

CASCADE #

自动删除依赖于被删除列或约束的对象（例如引用该列的视图），并进而删除依赖于那些对象的所有对象（参见 Section 5.15）。

RESTRICT #

如果有任何依赖对象，则拒绝删除该列或约束。这是默认行为。

trigger_name #

要禁用或启用的单个触发器的名称。

ALL #

禁用或启用属于该表的所有触发器。（如果其中任何触发器是内部生成的约束触发器，例如那些用于实现外键约束或可延迟的唯一约束和排他约束的触发器，则需要超级用户权限。）

USER #

禁用或启用属于该表的所有触发器，但内部生成的约束触发器除外，例如那些用于实现外键约束或可延迟的唯一约束和排他约束的触发器。

index_name #

现有索引的名称。

storage_parameter #

表存储参数的名称。

value #

表存储参数的新值。取决于参数的不同，这可能是一个数字或一个词。

parent_table #

要与该表关联或解除关联的父表。

new_owner #

表的新所有者的用户名。

new_access_method #

该表将被转换到的访问方法的名称。

new_tablespace #

该表将被移动到的表空间的名称。

new_schema #

该表将被移动到的模式的名称。

partition_name #

要作为新分区附加到该表或要从该表分离的表的名称。

partition_bound_spec #

新分区的分区边界规范。其语法的更多细节请参考 CREATE TABLE。

注记

关键字 COLUMN 是一个噪声词，可以省略。

当使用 ADD COLUMN 添加一个列并指定了一个非易失的 DEFAULT 时，默认值会在语句执行时求值，结果被存储在表的元数据中，当访问任何现有行时将返回该结果。该值只有在表被重写时才会被实际应用，这使得 ALTER TABLE 即使在大型表上也运行得非常快。如果没有指定列约束，则使用 NULL 作为 DEFAULT。在这两种情况下都不需要重写表。

添加一个带有易失 DEFAULT（例如 clock_timestamp()）的列、一个存储生成列、一个标识列，或者一个带有含约束的域数据类型的列，都会导致整个表及其索引被重写。添加虚拟生成列则从不需要重写。

更改现有列的类型通常会导致整个表及其索引被重写。作为一个例外，当更改现有列的类型时，如果 USING 子句不改变列的内容，并且旧类型可以二进制兼容地转换为新类型，或者是新类型上的无约束域，那么就不需要重写表。但是，除非系统能够验证新索引在逻辑上与现有索引等价，否则索引仍然会被重建。例如，如果一个列的排序规则被更改了，就需要重建索引，因为新的排序顺序可能不同。不过，在没有排序规则更改的情况下，可以把一个列从 text 更改为 varchar（或反过来）而无需重建索引，因为这些数据类型的排序方式完全相同。

对于大型表，表和/或索引的重建可能需要相当长的时间，并且会临时需要多达两倍的磁盘空间。

添加 CHECK 或 NOT NULL 约束需要扫描表以验证现有的行满足该约束，但不需要重写表。如果一个 CHECK 约束以 NOT ENFORCED 方式添加，则不会执行任何验证。

类似地，在附加一个新分区时，可能会对该分区进行扫描，以验证现有的行满足分区约束。

提供在单个 ALTER TABLE 中指定多种更改这一选项的主要原因是，这样可以把多次表扫描或重写合并为对表的一次遍历。

扫描一个大表来验证新的外键、检查或非空约束可能需要很长时间，并且在该 ALTER TABLE ADD CONSTRAINT 命令提交之前，对该表的其他更新都会被阻塞。NOT VALID 约束选项的主要目的是减少添加约束对并发更新的影响。使用 NOT VALID 时，ADD CONSTRAINT 命令不会扫描表，并且可以立即提交。之后，可以发出 VALIDATE CONSTRAINT 命令来验证现有的行满足该约束。验证步骤不需要阻塞并发更新，因为它知道其他事务将对它们插入或更新的行强制执行该约束；只需要检查先前已存在的行。因此，验证只会获取被修改表上的 SHARE UPDATE EXCLUSIVE 锁。（如果该约束是外键，那么还需要在被该约束引用的表上获取一个 ROW SHARE 锁。）除了提高并发性之外，在已知表中包含先前存在的违规数据的情况下，使用 NOT VALID 和 VALIDATE CONSTRAINT 也很有用。一旦约束建立起来，就不能再插入新的违规数据，而已存在的问题可以从容地加以纠正，直到 VALIDATE CONSTRAINT 最终成功。

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

要强制立即回收被删除列占用的空间，可以执行某种会重写整个表的 ALTER TABLE 形式。这将导致重建每一行，其中被删除的列被替换为空值。

ALTER TABLE 的重写形式不是 MVCC 安全的。在表被重写之后，如果并发事务使用的是重写发生之前获取的快照，那么该表在它们看来将是空的。更多细节请参见 Section 13.6。

SET DATA TYPE 的 USING 选项实际上可以指定任何涉及该行旧值的表达式；也就是说，它不仅可以引用正在被转换的列，还可以引用其他列。这使得使用 SET DATA TYPE 语法可以进行非常通用的转换。由于这种灵活性，USING 表达式不会被应用于该列的默认值（如果有的话）；其结果可能不是一个默认值所要求的常量表达式。这意味着当从旧类型到新类型没有隐式或赋值造型时，即使提供了 USING 子句，SET DATA TYPE 也可能无法转换默认值。在这种情况下，先用 DROP DEFAULT 删除默认值，执行 ALTER TYPE，然后再使用 SET DEFAULT 添加一个合适的新默认值。类似的考虑也适用于涉及该列的索引和约束。

如果一个表有任何后代表，则不允许在父表中添加、重命名或更改某个列的类型而不对后代做同样的操作。这确保后代始终拥有与父表匹配的列。类似地，不能只在父表中重命名一个 CHECK 约束而不在所有后代中也重命名它，这样父表与其后代之间的 CHECK 约束才能保持匹配。（不过，该限制不适用于基于索引的约束。）此外，因为从父表中选择也会从其后代中选择，所以父表上的约束不能被标记为有效，除非它对那些后代也被标记为有效。在所有这些情况下，ALTER TABLE ONLY 都会被拒绝。

递归的 DROP COLUMN 操作只有在后代没有从任何其他父表继承该列、并且从未对该列有过独立定义的情况下，才会移除后代表的列。非递归的 DROP COLUMN（即 ALTER TABLE ONLY ... DROP COLUMN）从不移除任何后代列，而是把它们标记为独立定义的列而不是继承的列。对于分区表，非递归的 DROP COLUMN 命令会失败，因为一个表的所有分区必须具有与分区根相同的列。

针对标识列的操作（ADD GENERATED、SET 等、DROP IDENTITY），以及 CLUSTER、OWNER 和 TABLESPACE 操作从不递归到后代表；也就是说，它们总是表现得就像指定了 ONLY 一样。影响触发器状态的操作会递归到分区表的分区（除非指定了 ONLY），但从不递归到传统继承的后代。添加约束只对未标记为 NO INHERIT 的 CHECK 约束进行递归。

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

有关有效参数的进一步说明，请参考 CREATE TABLE。Chapter 5 中有关于继承的更多信息。

示例

向一个表添加一个 varchar 类型的列： 

ALTER TABLE distributors ADD COLUMN address varchar(30);

这将导致表中所有现有的行在新列上被填充空值。

添加一个带有非空默认值的列： 

ALTER TABLE measurements
  ADD COLUMN mtime timestamp with time zone DEFAULT now();

现有的行将被填入当前时间作为新列的值，之后新插入的行将接收到它们被插入的时间。

添加一个列，并用一个与稍后将要使用的默认值不同的值来填充它： 

ALTER TABLE transactions
  ADD COLUMN status varchar(30) DEFAULT 'old',
  ALTER COLUMN status SET default 'current';

现有的行将被填入 old，但后续命令的默认值将是 current。其效果与把这两个子命令作为单独的 ALTER TABLE 命令发出时的效果相同。

从一个表中删除一个列： 

ALTER TABLE distributors DROP COLUMN address RESTRICT;

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

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

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

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();

重命名一个现有的列： 

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 distfk FOREIGN KEY (address) REFERENCES addresses (address);

以一种对其他工作影响最小的方式向一个表添加外键约束： 

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

向一个表添加（多列）唯一约束： 

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

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

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

把一个表移动到不同的表空间： 

ALTER TABLE distributors SET TABLESPACE fasttablespace;

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

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

重新创建一个主键约束，并且在重建索引期间不阻塞更新： 

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

把一个分区附加到一个范围分区表： 

ALTER TABLE measurement
    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

把一个分区附加到一个列表分区表： 

ALTER TABLE cities
    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

把一个分区附加到一个哈希分区表： 

ALTER TABLE orders
    ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

把一个默认分区附加到一个分区表： 

ALTER TABLE cities
    ATTACH PARTITION cities_partdef DEFAULT;

从一个分区表分离一个分区： 

ALTER TABLE measurement
    DETACH PARTITION measurement_y2015m12;

兼容性

形式 ADD [COLUMN]、DROP [COLUMN]、DROP IDENTITY、RESTART、SET DEFAULT、SET DATA TYPE（不含 USING）、SET GENERATED 以及 SET sequence_option 符合 SQL 标准。形式 ADD table_constraint 在省略 USING INDEX 和 NOT VALID 子句并且约束类型是 CHECK、UNIQUE、PRIMARY KEY 或 REFERENCES 之一时符合 SQL 标准。其他形式是 PostgreSQL 对 SQL 标准的扩展。此外，在单个 ALTER TABLE 命令中指定多种操作的能力也是一种扩展。

ALTER TABLE DROP COLUMN 可以用来删除表中唯一的列，从而留下一个零列的表。这是对 SQL 的一个扩展，因为 SQL 不允许零列的表。

另请参阅

CREATE TABLE
上一页 上一级 下一页
ALTER SYSTEM 首页 ALTER TABLESPACE