ALTER TABLE

Description

ALTER TABLE变更一个现存表的定义。它有好几种子形式：

除了RENAME和SET SCHEMA之外所有动作都可以 捆绑在一个多次修改列表中并行使用。比如，可以在一个命令里增加几个字段和/或 修改几个字段的类型。对于大表，这么做特别有用，因为只需要对该表做一次处理。

要使用ALTER TABLE，你必须拥有该表。要修改一个表的模式，你还必 须在新模式上拥有CREATE权限。要把该表添加为一个父表的新 子表，你必须同时拥有父表。要修改所有者，你还必须是新的所有角色的直接或间接 成员，并且该成员必须在此表的模式上有CREATE权限。这些限制 强制了修改该所有者不会做任何通过删除和重建表不能做的事情。不过，超级用户可 以以任何方式修改任意表的所有权。

参数

注意

COLUMN关键字是多余的，可以省略。

如果用ADD COLUMN增加一个字段，那么所有表中现有行 都初始化为该字段的缺省值(如果没有声明DEFAULT子句，那么就是 NULL)。

通过非空默认值添加一个列或者改变一个原有列的类型需要整个要被重写的表和索引。 这对于大型表可能需要大量时间；这将暂时需要两倍的磁盘空间。添加或者删除一个系统 oid列同样需要重写整个表。

增加一个CHECK或NOT NULL约束将会扫描该表以保证 现有的行符合约束要求。

提供在一个ALTER TABLE里面声明多个修改的主要原因是原先需要的 对表的多次扫描和重写可以组合成一个步骤。

DROP COLUMN命令并不是物理上把字段删除，而只是简单地 把它标记为对 SQL 操作不可见。随后对该表的插入和更新将在该字段存储一个 NULL 。 因此，删除一个字段是很快的，但是它不会立即释放表在磁盘上的空间，因为被删除 了的字段占据的空间还没有回收。这些空间将随着现有的行的更新而得到回收。 (在删除系统oid列时，这些语句不会应用；这是做了一个直接的重写。)

SET DATA TYPE要求重写整个表的特性有时候是一个优点，因为重写的过程 消除了表中没用的空间。比如，要想立刻回收被一个已经删除的字段占据的空间，最快的 方法是：

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

这里的anycol是任何在表中还存在的字段，而anytype是和该 字段的原类型一样的类型。这样的结果是在表上没有任何可见的语意的变化，但是这个命 令强迫重写，这样就删除了不再使用的数据。

SET DATA TYPE的USING选项实际上可以声明涉及该行 旧值的任何表达式；也就是说，它可以引用除了正在被转换的字段之外其它的字段。这样， 就可以用SET DATA TYPE语法做非常普遍性的转换。因为这个灵活性， USING表达式并没有作用于该字段的缺省值(如果有的话)；结果可能 不是缺省表达式要求的常量表达式。这就意味着如果从旧类型到新类型没有隐含或者赋值 转换的话，那么即使存在USING子句，SET DATA TYPE也 可能无法把缺省值转换成新的类型。在这种情况下，应该用DROP DEFAULT先 删除缺省，执行SET DATA TYPE，然后使用SET DEFAULT增加一个 合适的新缺省值。类似的考虑也适用于涉及该字段的索引和约束。

如果表有任何后代表，那么如果不在后代表上做同样的修改的话，就不允许在父表上增加、 重命名、修改一个字段的类型，也就是说，ALTER TABLE ONLY将被拒绝。 这样就保证了后代表总是有和父表匹配的字段。

一个递归DROP COLUMN操作将只有在后代表并不从任何其它父表中继 承该字段并且从来没有独立定义该字段的时候才能删除一个后代表的字段。一个非递归的 DROP COLUMN(也就是ALTER TABLE ONLY ... DROP COLUMN) 从来不会删除任何后代字段，而是把他们标记为独立定义的(而不是继承的)。

TRIGGER,CLUSTER,OWNER,TABLESPACE 行为绝不会递归到后代表；也就是说，它们的行为就像总是声明了ONLY一样。 添加一个约束只能在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 时间戳的 integer 字段转化成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 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 ONLY distributors DROP CONSTRAINT zipchk;

(此检查约束仅存在于子表中。)

向表中增加一个外键约束：

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

给表增加一个(多字段)唯一约束：

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 的扩展，它不允许零字段表。