ALTER TABLE

PostgreSQL 9.6.0 手册
上一页	上一级		下一页

---

名称

ALTER TABLE -- 更改一个表的定义

大纲

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 ]

其中action 是以下之一：

    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 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}

并且 table_constraint_using_index 是：

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

描述

ALTER TABLE更改一个现有表的定义。下文描述了 几种形式。注意每一种形式所要求的锁级别可能不同。如果没有明确说明，将会 持有一个ACCESS EXCLUSIVE所。当列出多个子命令时，所 持有的锁将是子命令所要求的最严格的那一个。

所有除了RENAME、 SET TABLESPACE和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);

通过一个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();

To rename an existing column:

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;

兼容性

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

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

另见

CREATE TABLE

---

上一页	起始页	下一页
ALTER SYSTEM	上一级	ALTER TABLESPACE