Postgresql 中文操作指南
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 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
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 ]
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
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 |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
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 ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( 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 referential_action ] [ ON UPDATE referential_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 ) } [ 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 [, ... ] ) ] }
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 子句。
-
当使用此表单时,列的统计信息将被删除,因此建议之后针对该表运行 ANALYZE 。
-
-
SET/DROP DEFAULT #
-
这些表单设置或移除列的默认值(其中移除等同于将默认值设置为 NULL)。新的默认值仅适用于后续的 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 约束,如果需要,仍然可以将此类约束添加到各个分区;也就是说,即使父级允许空值,子级也可以禁止空值,但反之则不然。
-
-
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 范围内设置;或者,将其设置为 -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 { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } #
-
此形式为列设置存储方式。它控制此列是内联持有还是在辅助的 TOAST 表中持有,以及数据是否应该压缩。 PLAIN 必须用于定长值,如 integer ,并且是内联的且未压缩的。 MAIN 用于可压缩的内联数据。 EXTERNAL 用于外部非压缩数据,而 EXTENDED 用于外部压缩数据。写入 DEFAULT 将存储方式设置为该列数据类型的默认模式。 EXTENDED 是支持非 PLAIN 存储的大多数数据类型的默认值。 EXTERNAL 的使用将使对非常大的 text 和 bytea 值的子字符串操作运行得更快,但代价是牺牲存储空间。请注意, ALTER TABLE …​ SET STORAGE 本身不会更改表中的任何内容;它只是设置在未来表更新期间预期执行的策略。请参见 Section 73.2 以了解更多信息。
-
-
SET COMPRESSION _compression_method_ #
-
此形式为列设置压缩方法,用于确定未来插入的值将如何压缩(如果存储方式允许压缩的话)。这不会导致表格被重写,所以现有数据仍然可用其他压缩方法压缩。如果使用 pg_restore 恢复该表格,那么所有值将使用配置的压缩方法重写。然而,当从另一个关系(例如,由 INSERT …​ SELECT )插入数据时,源表格中的值不一定会解压缩,所以任何之前压缩的数据可能会保留其已有的压缩方法,而不是使用目标列的压缩方法重新压缩。支持的压缩方法包括 pglz 和 lz4 。( lz4 仅在 PostgreSQL 构建时使用 —​with-lz4 时可用。)此外, compression_method 可以为 default ,它将在数据插入时咨询 default_toast_compression 设置,以选择要使用的方法。
-
-
ADD _table_constraint [ NOT VALID ]_ #
-
此形式使用与 CREATE TABLE 相同约束语法的约束为表添加新约束,加上选项 NOT VALID ,它目前仅允许用于外键和检查约束。
-
通常情况下,此形式将在表中进行扫描,以验证表中所有现有行均满足新约束。但是,如果使用了 NOT VALID 选项,则会跳过此可能很长的扫描。对于后续插入或更新,仍将使用约束(也就是说,在这种情况下插入或更新会失败,除非在所引用的表中有一行匹配,或者除非新行与指定的检查条件匹配)。但是,在通过使用 NOT VALID 选项对其进行验证之前,数据库不会假定此约束对表中的所有行有效。有关如何使用 NOT VALID 选项的详细信息,请参见下面的 Notes 。
-
尽管 ADD _table_constraint_ 的大多数形式需要 ACCESS EXCLUSIVE 锁,但 ADD FOREIGN KEY 只需要 SHARE ROW EXCLUSIVE 锁。请注意,除了在声明约束的表的锁之外, ADD FOREIGN KEY 还将在所引用的表上获取 SHARE ROW EXCLUSIVE 锁。
-
将唯一或主键约束添加到分区表时应用其他限制;请参见 CREATE TABLE 。此外,当前不能将分区表的外键约束声明为 NOT VALID 。
-
-
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 命令建立的类似。尤其是,删除该约束也会使该索引消失。
-
此表单当前不支持分区表。
-
-
ALTER CONSTRAINT #
-
此表单更改以前创建的约束的属性。目前,只能更改外键约束。
-
-
VALIDATE CONSTRAINT #
-
此表单验证以前作为 NOT VALID 创建的外键或检查约束,通过扫描表来确保没有约束不满足的行。如果约束已标记为有效,则不会发生任何情况。(请参见下方的 Notes 了解此命令的有效性的解释。)
-
此命令获取 SHARE UPDATE EXCLUSIVE 锁。
-
-
DROP CONSTRAINT [ IF EXISTS ] #
-
此表单丢弃表上指定的约束,以及约束下的任何索引。如果指定了 IF EXISTS ,并且约束不存在,则不会引发错误。在这种情况下,会发出通知。
-
-
DISABLE / ENABLE [ REPLICA | ALWAYS ] TRIGGER #
-
这些表单配置触发器属于表的触发。系统仍然知道已禁用的触发器,但在发生触发事件时不执行该触发器。(对于延迟触发器,在事件发生时检查启用状态,而不是在实际执行触发器函数时。)可以禁用或启用通过名称指定的单个触发器,或表上的所有触发器,或仅用户触发器(此选项不包括内部生成的约束触发器,例如用于实现外键约束或可延迟唯一性和排除约束的触发器)。禁用或启用内部生成的约束触发器需要超级用户权限;应该谨慎执行此操作,因为如果未执行触发器,当然无法保证约束的完整性。
-
触发器触发机制也受配置变量 session_replication_role 影响。只要启用了触发器(默认方式),当复制角色为“源”(默认方式)或“本地”时,它们将触发。配置为 ENABLE REPLICA 的触发器仅在会话处于“副本”模式下才会触发,而配置为 ENABLE ALWAYS 的触发器会触发而不考虑当前的复制角色。
-
此机制的影响在于,在默认配置中,触发器不会在副本上触发。这非常有用,因为如果触发器在源上用于在表之间传播数据,那么复制系统也将复制已传播的数据;因此,触发器不应该在副本上第二次触发,因为那会导致重复。但是,如果出于其他目的(例如创建外部警报)使用触发器,则可以将其设置为 ENABLE ALWAYS ,以便在副本上也触发它。
-
当此命令应用于分区表时,分区中相应的克隆触发器的状态也会更新,除非指定了 ONLY 。
-
此命令获取 SHARE ROW EXCLUSIVE 锁。
-
-
DISABLE / ENABLE [ REPLICA | ALWAYS ] RULE #
-
这些表单配置表中属于该表的重写规则的触发。系统仍然知道已禁用的规则,但在查询重写期间不会应用该规则。禁用/启用触发器的语义也一样。此配置对于 ON SELECT 规则将被忽略,这些规则始终应用以便在当前会话处于非默认复制角色时也能使视图正常工作。
-
规则触发机制也受配置变量 session_replication_role 的影响,类似于上面描述的触发器。
-
-
DISABLE / ENABLE ROW LEVEL SECURITY #
-
这些表单控制对表具有的行安全策略的应用。如果启用了,并且表没有策略存在,那么会应用默认拒绝策略。请注意,即使禁用了行级安全,对表仍然可能有策略存在。在这种情况下,策略将 not 应用,并且策略将被忽略。另请参阅 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 #
-
此表单通过重写来更改表访问方法。有关更多信息,请参见 Chapter 63 。
-
-
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 )。它不能应用于临时表。
-
这也更改了链接到表(对于标识列或序列列)的任何序列的持久性。但是,也有可能单独更改此类序列的持久性。
-
-
SET ( _storage_parameter [= value ] [, … ] )_ #
-
此表单更改表的存储参数(一个或多个)。有关可用参数的详细信息,请参阅 CREATE TABLE 文档中的 Storage Parameters 。请注意,此命令不会立即修改表内容;根据参数,您可能需要重写表以获得所需效果。可以使用 VACUUM FULL 、 CLUSTER 或强制表重写的其中一种 ALTER TABLE 表单来完成。对于计划程序相关的参数,更改将在下次锁定表时生效,因此不会影响当前执行的查询。
-
SHARE UPDATE EXCLUSIVE 针对填充因子、toast 和自动清理存储参数,以及计划程序参数 parallel_workers 获取锁。
-
-
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 已经形成该表一样。表的列名和类型列表必须与复合类型的列名和类型列表完全匹配。表不得从任何其他表继承。这些限制确保 CREATE TABLE OF 会允许等效表定义。
-
-
NOT OF #
-
此表单将键入表与其类型分离开来。
-
-
OWNER TO #
-
此表单将表、序列、视图、物化视图或外表的拥有者更改为指定的用户。
-
-
REPLICA IDENTITY #
-
此表单将写入预写式日志的信息更改为识别已更新或已删除的行。在大多数情况下,每一列的旧值仅在与新值不同时才被记录;但是,如果旧值被存储在外部,它总是被记录,无论它是否被更改。只有在使用逻辑复制时,此选项才会生效。
-
-
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 约束。当前不考虑 FOREIGN KEY 约束。父表中的 UNIQUE 和 PRIMARY KEY 约束将在分区中创建,如果它们不存在。如果要附加的表的任何 CHECK 约束被标记为 NO INHERIT ,该命令将会失败;此类约束必须在没有 NO INHERIT 子句的情况下重新创建。
-
如果新分区是个普通表,则会执行一个全表扫描来检查表中现有的行是否违反了分区约束。可以通过在运行此命令之前向表中添加一个允许仅具有满足所需分区约束的行一个有效的 CHECK 约束,来避免此扫描。 CHECK 约束将用来确定无需扫描表来验证分区约束。但是,如果任何分区键是一个表达式,并且分区不接受 NULL 值,则此方法将不起作用。如果附加一个将不接受 NULL 值的列表分区,除非它是一个表达式,还要向分区键列添加一个 NOT NULL 约束。
-
如果新分区是一个外表,则不会执行任何操作来验证外表中的所有行是否服从分区约束。(请参阅 CREATE FOREIGN TABLE 中有关外表上的约束的讨论。)
-
当一个表有默认分区时,定义一个新分区将更改默认分区的分区约束。默认分区不能包含任何需要移动到新分区中的行,并且将对其进行扫描以验证是否没有此类行存在。这个扫描,如新分区的扫描一样,可以在存在一个合适的 CHECK 约束时避免。同样地,就像新分区的扫描一样,当默认分区是一个外表时,它总是会被跳过。
-
附加一个分区将会获取父表上的一个 SHARE UPDATE EXCLUSIVE 锁以及附加表的 ACCESS EXCLUSIVE 锁(如果有默认分区),并且在该默认分区上获取一个锁。
-
如果要附加的表本身是一个分区表,则还必须对所有子分区保持锁定。同样,如果默认分区本身是一个分区表,也是如此。可以通过添加 CHECK 约束(如 Section 5.11.2.2 中所述),来避免锁定子分区。
-
-
DETACH PARTITION _partition_name [ CONCURRENTLY | FINALIZE ]_ #
-
此表单分离目标表的指定分区。分离的分区继续作为一个独立表存在,但不再与分离其的表有任何联系。任何附加到目标表索引的索引都被分离。任何作为目标表中索引克隆创建的触发器都会被移除。 SHARE 锁被获取到任何在外部密钥约束中引用此分区表的表中。
-
如果指定了 CONCURRENTLY ,它将使用一个更低级别的锁来运行,以避免阻塞可能正在访问分区表的其它会话。在此模式下,在内部会使用两个事务。在第一个事务期间, SHARE UPDATE EXCLUSIVE 锁被获取到父表和分区上,并且分区被标记为正在分离;在那时,事务被提交,并且等待所有其它使用分区表的会话。一旦所有那些事务完成,第二个事务将在分区表上获取 SHARE UPDATE EXCLUSIVE ,在分区上获取 ACCESS EXCLUSIVE ,并且分离过程完成。 CHECK 约束(复制分区约束)被添加到分区。 CONCURRENTLY 不能在事务块中运行,并且如果分区表包含默认分区,则不允许使用 CONCURRENTLY 。
-
如果指定了 FINALIZE ,则一个先前取消或中断的 DETACH CONCURRENTLY 调用将会完成。一次,分区表中最多只有一个分区可以挂起分离。
-
Note
在不需要长期阻塞表更新的情况下,添加一个使用现有索引的约束,在某些情况下可能会有所帮助。要执行此操作,请使用 CREATE INDEX CONCURRENTLY 创建索引,然后使用此语法将其安装为官方约束。请参阅以下示例。
除 RENAME 、 SET SCHEMA 、 ATTACH PARTITION 和 DETACH PARTITION 之外的作用于单个表的 ALTER TABLE 的所有形式可以合并到一起的多个更改列表中。例如,可以在单个命令中添加多个列和/或更改多个列的类型。对于大型表来说,这一点特别有用,因为只需要遍历一次表。
您必须拥有表才能使用 ALTER TABLE 。要更改表的模式或表空间,您还必须在新的模式或表空间上拥有 CREATE 权限。要将表作为父表的新的子项添加,您还必须拥有该父表。此外,要将表附加为该表的新的分区,您还必须拥有该要附加的表。要更改所有者,您必须能够将 SET ROLE 授予新的所有者角色,该角色还必须在表的模式上拥有 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.14 )。
-
-
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 ,以了解更多关于其语法的详细信息。
-
Notes
关键词 COLUMN 是噪音,可以省略。
当使用 ADD COLUMN 添加列和指定非易失性 DEFAULT 时,在语句时计算默认值并将结果存储在表的元数据中。该值将用于所有现有行的列。如果未指定 DEFAULT ,则使用 NULL。两种情况下都不需要重写表。
使用易失性 DEFAULT 添加列或更改现有列的类型将需要重写整个表及其索引。作为例外,在更改现有列的类型时,如果 USING 子句不更改列内容,并且旧类型可以强制转换为新类型或对新类型进行无约束域转换,则不需要表重写。然而,必须始终重建索引,除非系统可以验证新索引与现有索引在逻辑上是等效的。例如,如果已更改列的排序规则,则总是需要重建索引,因为新的排序顺序可能不同。然而,在不存在排序规则更改的情况下,列可以从 text 更改为 varchar (或反之),而无需重建索引,因为这些数据类型按相同方式排序。对于大表,表和/或索引重建可能需要大量时间;并且暂时需要多达两倍的磁盘空间。
添加 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 操作不可见。表中随后的插入和更新操作将为该列存储一个空值。因此,删除列很快,但不会立即减少表的磁盘大小,因为不会回收已删除列占用的空间。随着现有行的更新,空间将随着时间的推移而回收。
要强制立即回收已删除列占用的空间,您可以执行 ALTER TABLE 的一种形式,它会对整个表执行重写。这会导致使用空值替换已删除列来重建每一行。
ALTER TABLE 的重写形式不是 MVCC 安全的。在表重写之后,如果使用在重写发生之前拍摄的快照,则该表对于并发事务将显示为空。请参阅 Section 13.6 了解更多详细信息。
SET DATA TYPE 的 USING 选项实际上可以指定涉及行的旧值的任何表达式;也就是说,它可以引用其他列以及正在转换的列。这允许使用 SET DATA TYPE 语法完成非常通用的转换。由于这种灵活性, USING 表达式不适用于列的默认值(如果有);结果可能不是作为默认值所需的常量表达式。这意味着当没有隐式或赋值从旧类型转换为新类型时, SET DATA TYPE 可能会无法转换默认值,即使提供了 USING 子句。在这种情况下,使用 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 提供了关于继承的更多信息。
Examples
为表添加 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);
通过 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 标准。其他形式则为 PostgreSQL 对 SQL 标准的扩展。另外,在单个 ALTER TABLE 命令中指定多个操作的能力也是一种扩展。
ALTER TABLE DROP COLUMN 可用于删除表格的列,留下一个零列表格。这是 SQL 的一项扩展,不允许零列表格。