ALTER TABLE

名字

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

语法概要

   ALTER TABLE [ ONLY ] name [ * ]
       action [, ... ]
   ALTER TABLE [ ONLY ] name [ * ]
       RENAME [ COLUMN ] column TO new_column
   ALTER TABLE name
       RENAME TO new_name
   ALTER TABLE name
       SET SCHEMA new_schema
   
   where action is one of:
   
       ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
       DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]
       ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
       ALTER [ COLUMN ] column SET DEFAULT expression
       ALTER [ COLUMN ] column DROP DEFAULT
       ALTER [ COLUMN ] column { SET | DROP } NOT NULL
       ALTER [ COLUMN ] column SET STATISTICS integer
       ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
       ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )
       ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
       ADD table_constraint [ NOT VALID ]
       ADD table_constraint_using_index
       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
       CLUSTER ON index_name
       SET WITHOUT CLUSTER
       SET WITH OIDS
       SET WITHOUT OIDS
       SET ( storage_parameter = value [, ... ] )
       RESET ( storage_parameter [, ... ] )
       INHERIT parent_table
       NO INHERIT parent_table
       OF type_name
       NOT OF
       OWNER TO new_owner
       SET TABLESPACE new_tablespace
   
   and table_constraint_using_index is:
   
       [ CONSTRAINT constraint_name ]
       { UNIQUE | PRIMARY KEY } USING INDEX index_name
       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

描述

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

ADD COLUMN

这种形式向表中增加一个新的列，列的说明语法和 CREATE TABLE 一样。

DROP COLUMN [ IF EXISTS ]

这种形式从表中删除一个列。列关联的索引和表约束也将被自动删除掉。如果任何表之外的对象依赖于这个列，例如外键约束，视图等，你可能会需要用 CASCADE。如果指定了 IF EXISTS ，那么当列不存在时也不会报错，只会产生一个通知。

SET DATA TYPE

这种类型改变表中一个列的类型。列关联的索引和简单的表约束也将重新解析建立时给出的表达式，然后自动转换以使用新数据类型。可选的 COLLATE 子句设定新列的字符序；如果没指定这个子句，新列上的字符序就用缺省值。可选的 USING 子句声明如何从旧的列值计算出新的列值； 如果省略，那么缺省的转换就是从旧类型向新类型的赋值转换。如果从旧数据类型到新类型没有隐含转换或者赋值转换， 那么必须提供一个 USING。

SET/DROP DEFAULT

这种形式为一个列设置或者删除缺省值。请注意缺省值只应用于随后的 INSERT 命令；它们不会修改表中已有行的值。我们也可以为视图创建缺省值， 这个时候它们是在视图的 ON INSERT 规则应用之前插入 INSERT 语句中去的。

SET/DROP NOT NULL

这些形式修改一个列是否允许有 NULL 值。 如果表在字段中包含非空值，那么你只可以 SET NOT NULL。

SET STATISTICS

这个形式为随后的 ANALYZE 操作设置每个列的统计采集目标（statistics-gathering target）。 目标的范围可以在 0 到 10000 之内设置；另外，把他设置为 -1 则表示重新恢复到使用系统缺省的统计目标(default_statistics_target)。 关于统计信息在 PostgreSQL 查询规划器的使用信息， 请参考Section 14.2。

SET ( attribute_option = value [, ... ] )

RESET ( attribute_option [, ... ] )

这个形式设置或重置每个属性的选项值。目前针对每个属性定义的选项只有n_distinct和n_distinct_inherited，这些选项设定的值覆盖后面执行 ANALYZE 操作时估计出的值的差异化分布信息。n_distinct 只影响表自己的统计信息，而n_distinct_inherited 则不但影响自己还会影响继承自它的孩子表。当设置为正数时，ANALYZE 会假定列就只包含了这么多个非空的差异化取值。当设置为负数（必须大于或等于-1）时，ANALYZE 会假定列的非空的差异化取值和表大小成线性关系；用于计算的精确的差异数为求平均后的值。在表的大小不断改变的时候很有用，因为乘以表中行数的乘法运算直到查询计划的时候才会发生。设置为0则恢复对差异化取值的默认估计方法。关于统计信息在 PostgreSQL 查询规划器的使用信息， 请参考Section 14.2。

SET STORAGE

这种形式为一个列设置存储模式。设置这个列是表中保存还是保存在一个附属的TOAST 表里，以及数据是否要压缩。 PLAIN 子句必需用于定长的数值，比如 integer，并且是表内存储的，不压缩的。 MAIN 子句用于表内存储的，可压缩的数据。 EXTERNAL 子句用于外部保存，不压缩的数据， 而 EXTENDED 子句用于外部的压缩数据。 大多数支持非 PLAIN 存储的数据类型缺省为 EXTENDED 。 使用 EXTERNAL 将令非常大的 text 和二进制值 的子串操作上更快， 付出的代价则是增加了存储空间。 请注意 SET STORAGE 本身并不改变表上的任何东西， 只是设置将来的表操作时，建议使用的策略。参阅 Section 55.2 获取更多信息。

ADD table_constraint [ NOT VALID ]

这种形式向表中增加一个新的约束，约束的说明语法除了 NOT VALID 子句外和 CREATE TABLE 一样。 NOT VALID 子句目前只针对外键约束。如果约束被标明为 NOT VALID ，则可能花费很长时间来检查表中所有现有行是否满足约束的操作就不做了。然而约束仍将在随后的插入和更新操作中检查（也就是说如果被引用表中没有匹配行的话，插入和更新操作将失败）。除非使用 VALIDATE CONSTRAINT 子句进行约束验证，否则数据库不能够假定表中所有数据行都满足约束条件。

ADD table_constraint_using_index

这种形式在表中已有的唯一索引的基础上向表中添加一个新的主键约束或唯一性约束。索引中的所有列都将被包含在约束中。

索引不可以有表达式列，也不可以是部分索引。另外，索引必须具有缺省顺序排序的b-tree索引。这个限制确保与通常使用ADD PRIMARY KEY 或 ADD UNIQUE 命令产生的约束是相等的。

如果设定有 PRIMARY KEY ，但是被索引的列并没有被标识为 NOT NULL，那么这个命令就会在索引包含的每个列上做 ALTER COLUMN SET NOT NULL 操作。这需要一个全表扫描以验证这些列没有包含null值。其他情况下，该操作执行的就非常快。

如果有设定约束名称，那么索引会被重新命名为这个约束的名字。其他情况下约束则会命名成索引的名字。

命令执行结束后，索引就被作为约束的一部分，与通常使用ADD PRIMARY KEY 或 ADD UNIQUE 命令产生的索引一样。强调一点，删除约束的同时也会删除这个索引。

Note： 利用已有索引添加约束可以缩小建立约束时更新表的阻塞时间。要达到这样的目的，可以使用 CREATE INDEX CONCURRENTLY 命令首先建立索引，然后使用上门的语法将之建立为约束。 参看下面的示例。

VALIDATE CONSTRAINT

这种形式对之前用NOT VALID子句创建的外键约束进行验证，全表扫描以确保没有不符合要求的行。如果约束已经被标记为有效的话，那么执行这条命令不会有任何效果。将约束的建立与约束的有效性验证分开的做法的意义在于，验证的时候需要上一些表锁，而约束的建立则不需要。

DROP CONSTRAINT [ IF EXISTS ]

这个形式删除表上的约束。如果指定了 IF EXISTS ，那么当约束不存在时也不会报错，只会产生一个通知。

DISABLE/ENABLE TRIGGER 这个形式关闭或者打开属于该表的触发器。 一个被关闭掉的触发器是系统仍然知道的，但是在触发器事件发生的时候不会被执行。 对于一个推迟了的触发器，在事件发生的时候会检查打开状态，而不是在函数实际执行的时候。 我们可以通过申明名字的方法打开或者关闭任意一个触发器， 或者是该表上的所有触发器，或者只是用户触发器（这个选项排除了那些用于实现外键约束的触发器）。 打开或者关闭约束触发器要求超级用户权限； 这么做的时候应该小心，因为如果触发器不执行的话，约束保证的数据完整性也就没有办法确保了。

CLUSTER 这种形式为将来的 CLUSTER 选项选择缺省索引。它实际上并不重新对表建簇。

SET WITHOUT CLUSTER 这种形式从表中删除最新使用的 CLUSTER 索引。 这样会影响将来那些没有声明索引的建簇操作。

SET WITHOUT OIDS 这种形式从表中删除 oid 系统字段。 它和 DROP COLUMN oid RESTRICT 完全相同， 只不过是如果表上已经没有 oid 字段了，那么它不会报告错误。

请注意，不存在某种 ALTER TABLE 的变种可以在删除了 OID 之后再把它们恢复回来。

RENAME RENAME 形式改变一个表的名字（或者是一个索引，一个序列，或者一个视图）或者是表中一个独立字段的名字。 它对存储的数据没有任何影响。

OWNER 这个形式改变表，序列或者视图的所有者为指定所有者。

SET TABLESPACE 这种形式把表的表空间修改为指定的表空间并且把与表相关的数据文件移动到新的表空间去。 如果在表上呦索引，则不会移动。但是他们可以通过额外的 SET TABLESPACE 命令移动。参阅 CREATE TABLESPACE。

RENAME RENAME 形式改变一个表（或者一个索引，序列，或者视图）的名字， 或者是表中独立字段的名字。它们对存储的数据没有影响。

SET SCHEMA 这种形式把表移动到另外一个模式。相关的索引，约束，以及 SERIAL 字段的序列都跟着移动。

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

要使用 ALTER TABLE，你必需拥有该表。 要修改一个表的模式，你必须在新的模式上有 CREATE 权限。要修改所有者，你必须还是新的所有角色的直接或者间接的成员， 并且该角色在该表的模式上必须拥有 CREATE 权限。 （这样的限制就保证了修改所有者这个动作和删除、重建该表能做到的动作没有什么区别。 不过，超级用户可以修改任何表的所有者。）

参数

table 试图更改的现存表（可能有模式修饰）的名称。 如果声明了 ONLY，则只更改该表。 如果没有声明 ONLY，则该表及其所有后代表（如果有）都被更新。 我们可以在表名字后面附加一个 * 表示后代表都被扫描，但是在目前的版本里，这是缺省行为。 （在7.1之前的版本，ONLY 是缺省的行为。）缺省可以通过改变配置选项 sql_inheritance 来改变。

column 现存或新的字段名称。

new_column 现存字段的新名称。

new_name 表的新名称。

type 新字段的类型，或者现存字段的新类型。

table_constraint 表的新的约束定义。

constraint_name 要删除的现有约束的名字。

trigger_name 要打开或者关闭的单个触发器的名字。

ALL 打开或者关闭属于该表的所有触发器。 （如果这些触发器中有外键约束，那么就要求超级用户权限。）

USER 打开或者关闭所有属于该表的非外键约束触发器。

index_name 要标记为建簇的表上面的索引名字。

CASCADE 自动删除依赖于被依赖字段或者约束的对象（比如，引用该字段的视图）。

RESTRICT 如果字段或者约束还有任何依赖的对象，则拒绝删除该字段。 这是缺省行为。

new_owner 该表的新所有者的用户名。

new_tablespace 这个表将要移动往的表空间名字。

new_schema 表将移动前往的模式的名字。

注意

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

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

用一个非空缺省增加一个字段或者是改变一个字段的现有类型会要求整个表的重写。 对于大表来说，这个操作可能会花很长时间；并且它还临时需要两倍的磁盘空间。

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

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

DROP COLUMN 命令并不是物理上把字段删除， 而只是简单地把它标记为 SQL 操作中不可见的。随后对该表的插入和更新将在该字段存储一个 NULL。 因此，删除一个字段是很快的，但是它不会立即缩减你的表在磁盘上的大小，因为被删除了的字段占据的空间还没有回收。 这些空间将随着现有的行的更新而得到回收。

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 部分获取更多有效参数的描述。 Chapter 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 时间戳的 integer 字段转化成 timestamp with time zone：

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