Note

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

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 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
    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 table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    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 WITH OIDS
    SET WITHOUT OIDS
    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_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and partition_bound_spec is:

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
  TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

and column_constraint is:

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

and table_constraint is:

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

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 ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Description

ALTER TABLE更改现有表的定义。下面介绍了几个子窗体。请注意，每个子窗体所需的锁定级别可能有所不同。除非明确指出，否则将获得ACCESS EXCLUSIVE锁。当给出多个子命令时，获得的锁将是任何子命令所需的最严格的锁。

如果此表是一个分区，则在父表中标记为NOT NULL的列将无法对它们执行DROP NOT NULL。要从所有分区中删除NOT NULL约束，请在父表上执行DROP NOT NULL。即使父级上没有NOT NULL约束，也可以根据需要将此约束添加到各个分区；也就是说，即使 parent 允许，孩子也可以禁止 null，反之则不行。

如果指定DROP IDENTITY IF EXISTS并且该列不是标识列，则不会引发任何错误。在这种情况下，将发出通知。

SET STATISTICS获得SHARE UPDATE EXCLUSIVE锁。

更改每个属性选项将获得SHARE UPDATE EXCLUSIVE锁定。

通常，此表单将导致对表的扫描，以验证表中所有现有的行均满足新约束。但是，如果使用NOT VALID选项，则会跳过此可能冗长的扫描。约束将仍然针对随后的插入或更新(即，对于外键而言，除非在引用表中有匹配的行，否则它们将失败，否则，除非新行与指定的检查匹配，否则它们将失败)健康)状况)。但是，除非使用VALIDATE CONSTRAINT选项对其进行验证，否则数据库不会假定该约束适用于表中的所有行。有关使用NOT VALID选项的更多信息，请参见下面的Notes。

尽管大多数形式的ADD table_constraint都需要ACCESS EXCLUSIVE锁，但ADD FOREIGN KEY仅仅需要SHARE ROW EXCLUSIVE锁。请注意，除了声明约束的表上的锁以外，ADD FOREIGN KEY还获得了对引用表的SHARE ROW EXCLUSIVE锁。

将唯一或主键约束添加到分区表时，还会应用其他限制；参见CREATE TABLE。另外，分区表上的外键约束目前可能尚未声明为NOT VALID。

索引不能有表达式列，也不能是部分索引。另外，它必须是具有默认排序 Sequences 的 b 树索引。这些限制确保索引等于由常规ADD PRIMARY KEY或ADD UNIQUE命令构建的索引。

如果指定了PRIMARY KEY，并且索引的列尚未标记NOT NULL，则此命令将尝试对每个此类列执行ALTER COLUMN SET NOT NULL。这需要全表扫描，以验证列不包含任何空值。在所有其他情况下，这都是快速的操作。

如果提供了约束名称，那么索引将被重命名以匹配约束名称。否则，约束将被命名为与索引相同。

执行此命令后，索引将由约束“拥有”，就像使用常规ADD PRIMARY KEY或ADD UNIQUE命令构建索引一样。特别是，删除约束将使索引也消失。

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

Note

在需要添加新约束而不长时间阻止表更新的情况下，使用现有索引添加约束可能会有所帮助。为此，请使用CREATE INDEX CONCURRENTLY创建索引，然后使用此语法将其安装为正式约束。请参见下面的示例。

触发器触发机制还受配置变量session_replication_role的影响。当复制角色为“原始”(默认)或“本地”时，将启用简单启用的触发器(默认)。仅当会话处于“副本”模式时，才会触发配置为ENABLE REPLICA的触发器，而无论当前的复制角色如何，都将触发配置为ENABLE ALWAYS的触发器。

此机制的效果是，在默认配置中，触发器不会在副本上触发。这很有用，因为如果在源上使用触发器在表之间传播数据，则复制系统还将复制传播的数据，并且触发器不应在副本上再次触发，因为这会导致重复。但是，如果将触发器用于其他目的(例如创建外部警报)，则将其设置为ENABLE ALWAYS可能是适当的，这样它也会在副本上触发。

此命令获得SHARE ROW EXCLUSIVE锁。

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

更改群集选项将获得SHARE UPDATE EXCLUSIVE锁定。

更改群集选项将获得SHARE UPDATE EXCLUSIVE锁定。

请注意，这不等于ADD COLUMN oid oid；会添加一个恰好名为oid的普通列，而不是系统列。

SHARE UPDATE EXCLUSIVE锁定将用于填充因子，吐司和自动真空存储参数，以及计划程序参数parallel_workers。

Note

虽然CREATE TABLE允许以WITH (storage_parameter)语法指定OIDS，但是ALTER TABLE不会将OIDS视为存储参数。而是使用SET WITH OIDS和SET WITHOUT OIDS表单来更改 OID 状态。

对于父级的所有CHECK约束，还必须有匹配的子表约束，但在父级中标记为不可继承(即用ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT创建)的约束除外。匹配的所有子表约束均不得标记为不可继承。当前不考虑UNIQUE，PRIMARY KEY和FOREIGN KEY约束，但是将来可能会改变。

使用FOR VALUES的分区对* partition_bound_spec *的语法与CREATE TABLE相同。分区绑定规范必须与目标表的分区策略和分区键相对应。要附加的表必须具有与目标表相同的所有列，并且不得更多。此外，列类型也必须匹配。而且，它必须具有目标表的所有NOT NULL和CHECK约束。目前不考虑FOREIGN KEY约束。如果父表中的UNIQUE和PRIMARY KEY约束不存在，则会在分区中创建它们。如果要附加的表的任何CHECK约束标记为NO INHERIT，则命令将失败；否则，命令将失败。此类约束必须在没有NO INHERIT子句的情况下重新创建。

如果新分区是常规表，则执行全表扫描以检查表中的现有行是否未违反分区约束。可以通过在表中添加有效的CHECK约束来避免这种扫描，该约束只允许在满足以下条件的行之前运行该命令： CHECK约束将用于确定不需要扫描表以验证分区约束。但是，如果任何分区键是一个表达式并且该分区不接受NULL值，则此方法不起作用。如果附加一个不接受NULL值的列表分区，也请向分区键列添加NOT NULL约束，除非它是一个表达式。

如果新分区是外部表，则不执行任何操作来验证外部表中的所有行均遵守分区约束。 (请参阅创建外表中有关外表约束的讨论。)

当表具有默认分区时，定义新分区将更改默认分区的分区约束。默认分区不能包含任何需要移动到新分区的行，并且将对其进行扫描以确认不存在任何行。如果存在适当的CHECK约束，则可以避免像新分区的扫描一样进行此扫描。就像扫描新分区一样，当默认分区是外部表时，总是会跳过它。

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

您必须拥有该表才能使用ALTER TABLE。要更改表的架构或表空间，您还必须对新架构或表空间具有CREATE特权。要将表添加为父表的新子级，您还必须拥有父表。另外，要将表作为表的新分区附加，您必须拥有要附加的表。要更改所有者，您还必须是新拥有角色的直接或间接成员，并且该角色在表的架构上必须具有CREATE特权。 (这些限制规定，更改所有者不会通过删除并重新创建表来执行您无法做的任何事情.但是，超级用户仍然可以更改任何表的所有权.)要添加列或更改列类型，或使用OF子句，您还必须对数据类型具有USAGE特权。

Parameters

Notes

关键字COLUMN是噪音，可以省略。

当在列中添加ADD COLUMN并指定了非易失性DEFAULT时，将在声明时评估默认值，并将结果存储在表的元数据中。该值将用于所有现有行的列。如果未指定DEFAULT，则使用 NULL。无论哪种情况都不需要重写表。

添加具有 volatile DEFAULT的列或更改现有列的类型将需要重写整个表及其索引。作为 exception，在更改现有列的类型时，如果USING子句不更改列的内容，并且旧类型可以二进制强制转换为新类型，或者不受限制地使用新类型，则无需重写表;但是受影响的列上的所有索引仍必须重建。添加或删除系统oid列还需要重写整个表。对于大型表，表和/或索引的重建可能会花费大量时间；并且暂时需要多达两倍的磁盘空间。

添加CHECK或NOT NULL约束要求扫描表以验证现有行是否满足约束，但不需要重写表。

类似地，在附加新分区时，可以对其进行扫描以验证现有行是否满足分区约束。

提供在单个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 操作不可见。表中随后的插入和更新操作将为该列存储一个空值。因此，删除列很快，但是不会立即减小表的磁盘大小，因为删除的列所占用的空间不会被回收。随着现有行的更新，空间将随着时间的推移而回收。 (当删除 system oid列时，这些语句不适用；需要立即重写才能完成.)

要立即回收被删除的列占用的空间，您可以执行ALTER TABLE形式之一来重写整个表。这将导致重建的每一行都将被删除的列替换为空值。

ALTER TABLE的重写形式不是 MVCC 安全的。在表重写之后，如果并发事务使用的是在重写发生之前拍摄的快照，则该表将对并发事务显示为空。有关更多详细信息，请参见Section 13.5。

SET DATA TYPE的USING选项实际上可以指定任何涉及该行的旧值的表达式；也就是说，它可以引用其他列以及要转换的列。这允许使用SET DATA TYPE语法完成非常通用的转换。由于具有这种灵 Active，因此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)的动作以及TRIGGER，CLUSTER，OWNER和TABLESPACE的动作从不递归到后代表；也就是说，它们始终像ONLY被指定一样起作用。添加约束仅对未标记NO INHERIT的CHECK约束递归。

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

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

Examples

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

要重命名现有列：

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;

Compatibility

形式ADD(不具有USING INDEX)，DROP [COLUMN]，DROP IDENTITY，RESTART，SET DEFAULT，SET DATA TYPE(不具有USING)，SET GENERATED和SET sequence_option符合 SQL 标准。其他形式是 SQL 标准的 PostgreSQL 扩展。同样，在单个ALTER TABLE命令中指定多个操纵的功能也是一种扩展。

ALTER TABLE DROP COLUMN可用于删除表的唯一列，而保留零列表。这是 SQL 的扩展，不允许使用零列表。

See Also

CREATE TABLE

上一章 首页 下一章