描述

ALTER TABLE更改现有表的定义。下面介绍几种子形式：

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

要使用ALTER TABLE，必须拥有该表。要更改表的模式或表空间，还必须在新模式或表空间上拥有CREATE权限。要将表作为父表的新子表添加，还必须拥有父表。要更改所有者，还必须是新所有者角色的直接或间接成员，并且该角色必须在表所在模式上拥有CREATE权限。（这些限制确保更改所有者不会执行任何无法通过删除并重新创建表完成的操作。不过，超级用户无论如何都可以更改任何表的所有权。）要添加列、更改列类型或使用OF子句，还必须在数据类型上拥有USAGE权限。

参数

注解

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

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

使用 DEFAULT 子句添加列，或更改现有列的类型，都需要重写整个表及其索引。作为更改现有列类型时的例外，如果USING子句不改变列内容，并且旧类型要么可以二进制强制转换为新类型，要么是新类型之上的无约束域，则不需要重写表；但受影响列上的任何索引仍必须重建。添加或移除系统oid列也需要重写整个表。对于大型表，重建表和/或索引可能需要相当长的时间，并且会暂时需要最多两倍的磁盘空间。

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

允许在单条ALTER TABLE命令中指定多项更改，主要是因为这样可以将多次表扫描或重写合并为一次遍历。

DROP COLUMN形式不会从物理上删除列，而只是使其对 SQL 操作不可见。表后续的插入和更新操作会为该列存储空值。因此，删除列的速度很快，但不会立即减少表在磁盘上的大小，因为被删除列占用的空间不会被回收。随着现有行被更新，空间会逐渐回收。（删除系统oid列时不适用这些说明；删除该列会立即重写表。）

若要强制立即回收已删除列所占的空间，可以执行任何一种会导致整表重写的ALTER TABLE形式。这样会重建每一行，并用空值替换被删除的列。

会重写表的ALTER TABLE形式对于 MVCC 来说并不安全。表重写完成后，如果并发事务使用的是在重写发生之前取得的快照，那么该表在这些并发事务看来会像一张空表。详见第 13.5 节。

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

如果表有任何后代表，则不允许只在父表中添加、重命名或更改列的类型，或重命名继承的约束，而不对后代表执行相同操作。也就是说，ALTER TABLE ONLY会被拒绝。这确保后代表始终拥有与父表匹配的列。

只有当某个后代表中的列既不是从其他父表继承而来，也从未有过该列的独立定义时，递归的DROP COLUMN操作才会移除该后代表中的此列。非递归的DROP COLUMN（即ALTER TABLE ONLY ... DROP COLUMN）永远不会移除任何后代列，而只会把它们标记为独立定义，而非继承得到。

TRIGGER、CLUSTER、OWNER和TABLESPACE操作永远不会递归到后代表；也就是说，它们的行为始终如同指定了ONLY。添加约束时，只有未标记为NO INHERIT的CHECK约束会递归。

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

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

示例

要添加类型为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);

要把一个包含 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 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;

兼容性

以下形式符合 SQL 标准：ADD（不带USING INDEX）、DROP、SET DEFAULT以及SET DATA TYPE（不带USING）。其他形式是PostgreSQL对 SQL 标准的扩展。此外，在单条ALTER TABLE命令中指定多个操作的能力也是一种扩展。

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

另见