描述

ALTER TABLE更改一个现有表的定义。它有若干种子形式：

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

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

参数

注解

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

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

增加一个带非空默认值的列或更改一个现有列的类型将要求重写整个表及其索引。这对于大型表可能需要相当长的时间，并且会暂时需要两倍的磁盘空间。

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

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

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

ALTER TYPE要求重写整个表这一事实有时反而是一个优点，因为重写过程会清除表中的任何死空间。例如，要立即回收一个被删除列占用的空间，最快的方式是：

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

其中anycol是表中任意一个剩余的列，而anytype是该列已有的类型。这不会对表产生任何语义上可见的改变，但该命令会强制重写，从而清除不再有用的数据。

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

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

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

TRIGGER、CLUSTER、OWNER 和 TABLESPACE 操作永远不会递归到后代表；也就是说，它们的行为始终如同指定了 ONLY。添加约束时，只有 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 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 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 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;

兼容性

ADD、DROP和SET DEFAULT形式符合 SQL 标准。其他形式都是 PostgreSQL对 SQL 标准的扩展。 此外，在单条ALTER TABLE命令中指定多个操作的能力也是一种扩展。

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