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
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 table_constraint_using_index is:

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

Description

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。

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

    • 这些形式改变了现有身份列基础的 Sequences。 * sequence_option *是ALTER SEQUENCE支持的选项，例如INCREMENT BY。

  • SET STATISTICS

    • 该表格为后续的ANALYZE操作设置了每个列的统计信息收集目标。可以在 0 到 10000 范围内设置目标。或者，将其设置为-1 以恢复为使用系统默认统计信息目标(default_statistics_target)。有关 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影响表及其继承子级收集的统计信息。当设置为正值时，ANALYZE将假定该列恰好包含指定数量的不同非空值。当设置为负值(必须大于或等于-1)时，ANALYZE将假定列中不同的非空值的数量在表的大小上是线性的；确切的计数将通过将估计的表大小乘以给定数字的绝对值来计算。例如，值-1 表示该列中的所有值都是不同的，而值-0.5 表示每个值平均出现两次。当表的大小随时间变化时，这很有用，因为直到查询计划时间才执行表中行数的乘法。将值指定为 0 可恢复为通常估计不同值的数量。有关 PostgreSQL 查询计划程序使用统计信息的更多信息，请参考Section 14.2。

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

  • SET STORAGE

    • 此表单设置列的存储模式。这控制此列是内联保留还是在辅助 TOAST 表中，以及是否应压缩数据。 PLAIN必须用于固定长度的值，例如integer，并且是内联的，未压缩的。 MAIN用于内联可压缩数据。 EXTERNAL用于外部未经压缩的数据，EXTENDED用于外部未经压缩的数据。 EXTENDED是大多数支持非PLAIN存储的数据类型的默认设置。使用EXTERNAL将使在非常大的text和bytea值上执行子字符串运算的速度更快，但会增加存储空间。请注意，SET STORAGE本身并不会更改表中的任何内容，它只是设置要在以后的表更新中采用的策略。有关更多信息，请参见Section 66.2。

  • ADD table_constraint [ NOT VALID ]

    • 此表单使用与CREATE TABLE相同的约束语法以及选项NOT VALID将新的约束添加到表中，该约束当前仅适用于外键和 CHECK 约束。

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

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

  • ADD table_constraint_using_index

    • 此表单根据现有的唯一索引向表中添加新的PRIMARY KEY或UNIQUE约束。索引的所有列都将包含在约束中。

索引不能有表达式列，也不能是部分索引。另外，它必须是具有默认排序 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创建索引，然后使用此语法将其安装为正式约束。请参见下面的示例。

  • ALTER CONSTRAINT

    • 此表单更改了先前创建的约束的属性。当前，只有外键约束可以更改。

  • VALIDATE CONSTRAINT

    • 此表单通过扫描表以确保没有不满足该约束的行来验证以前创建为NOT VALID的外键或检查约束。如果约束已被标记为有效，则什么也不会发生。 (有关此命令的用途的说明，请参见下面的Notes。)

  • DROP CONSTRAINT [ IF EXISTS ]

    • 此表单将指定的约束放到表上。如果指定IF EXISTS且该约束不存在，则不会引发任何错误。在这种情况下，将发出通知。

  • DISABLE / ENABLE [ REPLICA | ALWAYS ] TRIGGER

    • 这些表格配置了触发属于该表的触发器。禁用的触发器对于系统仍然是已知的，但是在其触发事件发生时不会执行。对于延迟的触发器，将在事件发生时而不是在实际执行触发器功能时检查启用状态。可以禁用或启用按名称指定的单个触发器，或表上的所有触发器，或仅禁用用户触发器(此选项不包括内部生成的约束触发器，例如用于实现外键约束或可延迟的唯一性和排除约束的那些)。禁用或启用内部生成的约束触发器需要超级用户特权。应该谨慎行事，因为如果不执行触发器，当然不能保证约束的完整性。触发器触发机制还受配置变量session_replication_role的影响。当复制角色为“原始”(默认)或“本地”时，将启用简单启用的触发器。仅当会话处于“副本”模式时，才会触发配置为ENABLE REPLICA的触发器，而无论当前复制模式如何，都将触发配置为ENABLE ALWAYS的触发器。

此命令获得SHARE ROW EXCLUSIVE锁。

  • DISABLE / ENABLE [ REPLICA | ALWAYS ] RULE

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

  • DISABLE / ENABLE ROW LEVEL SECURITY

    • 这些表格控制着属于该表的行安全策略的应用。如果启用并且该表不存在任何策略，则将应用默认拒绝策略。请注意，即使禁用了行级安全性，表的策略也可以存在-在这种情况下，将不应用策略，并且将忽略该策略。另请参见CREATE POLICY。

  • NO FORCE / FORCE ROW LEVEL SECURITY

    • 当用户是表所有者时，这些表格控制着属于该表的行安全策略的应用。如果启用，则当用户是表所有者时，将应用行级安全策略。如果禁用(默认设置)，则当用户是表所有者时，行级安全性将不适用。另请参见CREATE POLICY。

  • CLUSTER ON

    • 此表单为以后的CLUSTER操作选择默认索引。它实际上并没有重新群集表。

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

  • SET WITHOUT CLUSTER

    • 该表格从表中删除最近使用的CLUSTER索引规范。这会影响以后未指定索引的集群操作。

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

  • SET WITH OIDS

    • 此表单将oid系统列添加到表中(请参见Section 5.4)。如果表已具有 OID，则不执行任何操作。

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

  • SET WITHOUT OIDS

    • 此表单从表中删除oid系统列。这与DROP COLUMN oid RESTRICT完全等效，除了如果已经没有oid列时它不会抱怨。

  • SET TABLESPACE

    • 该表单将表的表空间更改为指定的表空间，并将与表关联的数据文件移动到新表空间。表中的索引(如果有)不会移动；但可以使用其他SET TABLESPACE命令分别移动它们。可以使用ALL IN TABLESPACE表单移动表空间中当前数据库中的所有表，该表单将锁定所有要先移动的表，然后再移动每个表。此表单还支持OWNED BY，它将仅移动指定角色所拥有的表。如果指定了NOWAIT选项，则如果该命令无法立即获取所有必需的锁，则该命令将失败。请注意，此命令不会移动系统目录，请根据需要使用ALTER DATABASE或显式ALTER TABLE调用。 information_schema关系不视为系统目录的一部分，将被移动。另请参见CREATE TABLESPACE。

  • SET { LOGGED | UNLOGGED }

    • 此表单将表从未记录更改为已记录，反之亦然(请参阅UNLOGGED)。它不能应用于临时表。

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

    • 该表格更改了表的一个或多个存储参数。有关可用参数的详细信息，请参见Storage Parameters。注意，此命令不会立即修改表的内容。根据参数，您可能需要重写表以获得所需的效果。可以使用VACUUM FULL，CLUSTER或ALTER TABLE形式之一来强制表重写。对于与计划者相关的参数，更改将在表下次锁定时生效，因此当前执行的查询将不受影响。

SHARE UPDATE EXCLUSIVE锁定将用于 fillfactor 和 autovacuum 存储参数，以及计划程序参数parallel_workers。

Note

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

  • RESET ( storage_parameter [, ... ] )

    • 该表格将一个或多个存储参数重置为其默认值。与SET一样，可能需要重写表才能完全更新表。

  • INHERIT parent_table

    • 此表单将目标表添加为指定父表的新子表。随后，针对父级的查询将包括目标表的记录。要作为子项添加，目标表必须已经包含与父项相同的所有列(它也可以具有其他列)。这些列必须具有匹配的数据类型，并且如果它们在父级中具有NOT NULL约束，那么它们在子级中也必须具有NOT NULL约束。

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

  • NO INHERIT parent_table

    • 该表格从指定父表的子表中删除目标表。对父表的查询将不再包括从目标表中提取的记录。

  • OF type_name

    • 此表将表链接到复合类型，就好像CREATE TABLE OF已经形成它一样。该表的列名和类型列表必须与组合类型的列表完全匹配； oid系统列的存在可以不同。该表不得从任何其他表继承。这些限制确保CREATE TABLE OF将允许等效的表定义。

  • NOT OF

    • 这种形式将类型化表与其类型分离。

  • OWNER

    • 此表单将表，序列，视图，实例化视图或外部表的所有者更改为指定的用户。

  • REPLICA IDENTITY

    • 该表格更改了写入预写日志的信息，以标识已更新或删除的行。该选项仅在使用逻辑复制时无效。 DEFAULT(非系统表的默认值)记录主键的列的旧值(如果有)。 USING INDEX记录命名索引覆盖的列的旧值，这些值必须是唯一的，不局部的，不可延迟的，并且仅包括标记为NOT NULL的列。 FULL记录该行中所有列的旧值。 NOTHING不记录有关旧行的信息。 (这是系统表的默认值.)在所有情况下，除非该行的新旧版本中至少要记录的列之一不同，否则不会记录任何旧值。

  • RENAME

    • RENAME表单更改表的名称(或索引，序列，视图，实例化视图或外部表)，表中单个列的名称或表的约束的名称。对存储的数据没有影响。

  • SET SCHEMA

    • 这种形式将表移动到另一个模式。表列拥有的关联索引，约束和序列也将移动。

  • ATTACH PARTITION partition_name FOR VALUES partition_bound_spec

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

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

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

  • DETACH PARTITION partition_name

    • 此表单分离目标表的指定分区。分离的分区 continue 作为独立表存在，但不再与分离该表的表有任何联系。

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

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

Parameters

  • IF EXISTS

    • 如果表不存在，请不要抛出错误。在这种情况下发出通知。

  • name

    • 要更改的现有表的名称(可选的模式限定)。如果在表名之前指定了ONLY，则仅更改该表。如果未指定ONLY，则该表及其所有后代表(如果有)将被更改。 (可选)可以在表名称后指定*，以明确指示包括后代表。

  • column_name

    • 新列或现有列的名称。

  • new_column_name

    • 现有列的新名称。

  • new_name

    • 表格的新名称。

  • data_type

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

  • table_constraint

    • 表的新表约束。

  • constraint_name

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

  • CASCADE

    • 自动删除依赖于所删除的列或约束的对象(例如，引用该列的视图)，并依次删除所有依赖于那些对象的对象(请参见Section 5.13)。

  • RESTRICT

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

  • trigger_name

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

  • ALL

    • 禁用或启用属于该表的所有触发器。 (如果任何触发器是内部生成的约束触发器，例如用于实现外键约束或可延迟的唯一性和排除约束的触发器，则这需要超级用户特权.)

  • USER

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

  • index_name

    • 现有索引的名称。

  • storage_parameter

    • 表存储参数的名称。

  • value

    • 表存储参数的新值。根据参数，它可能是数字或单词。

  • parent_table

    • 父表要与此表关联或取消关联。

  • new_owner

    • 表的新所有者的用户名。

  • new_tablespace

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

  • new_schema

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

  • partition_name

    • 要附加为新分区或与该表分离的表的名称。

  • partition_bound_spec

    • 新分区的分区绑定规范。有关相同语法的更多详细信息，请参考CREATE TABLE。

Notes

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

当用ADD COLUMN添加列时，表中的所有现有行都用该列的默认值初始化(如果未指定DEFAULT子句，则为 NULL)。如果没有DEFAULT子句，则这仅仅是元数据更改，不需要立即更新表的数据；而是在读取时提供添加的 NULL 值。

添加带有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添加合适的新默认值。类似的考虑适用于涉及该列的索引和约束。

如果表有任何后代表，则不允许在父表中添加，重命名或更改列的类型，而无需对后代表进行任何操作。这样可以确保后代始终具有与父代匹配的列。同样，不能在所有后代中重命名约束时也不能在其父级中重命名约束，因此约束在父级及其后代之间也会匹配。另外，由于从父级中进行选择也从其后代中进行选择，因此除非将父级约束也标记为对这些后代有效，否则不能将其标记为有效。在所有这些情况下，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 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

Name PostgreSQL 中文文档
Version
Language
Badge
Last Updated 2021-07-07T13:01:31