©
This document uses PHP Chinese website manual Release
ALTER TABLE
Name
ALTER TABLE -- 修改表的定义Synopsis
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 type [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ 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
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
OWNER TO new_owner
SET TABLESPACE new_tablespace
Description
ALTER TABLE变更一个现存表的定义。它有好几种子形式：
- ADD COLUMN
它使用和CREATE TABLE一样的语法向表中增加一个新的字段。
- DROP COLUMN [ IF EXISTS ]
它从表中删除一个字段。和这个字段相关的索引和表约束也会被自动删除。 如果任何表之外的对象依赖于这个字段，必须说CASCADE，比如外键参考、视图等等。
- SET DATA TYPE
它改变表中一个字段的类型。该字段涉及的索引和简单的表约束将被自动地转换为使用新的字段类型，方法是重新分析最初提供的表达式。可选的USING子句声明如何从旧的字段值里计算新的字段值；如果省略，那么缺省的转换就是从旧类型向新类型的赋值转换。如果从旧数据类型到新类型没有隐含或者赋值的转换，那么必须提供一个USING
- SET/DROP DEFAULT
这种形式为一个字段设置或者删除缺省值。请注意缺省值只应用于随后的INSERT命令；它们不会修改表中已经存在的行。也可以为视图创建缺省，这个时候它们是在视图的ON INSERT规则应用之前插入INSERT语句中去的。
- SET/DROP NOT NULL
它修改一个字段是否允许 NULL 值或者拒绝 NULL 值。如果表在字段中包含非 NULL ，那么你只可以SET NOT NULL
- SET STATISTICS
-
SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] ) 它设置或者重置每属性选项。目前，唯一定义的每属性选项是n_distinct 和n_distinct_inherited，这些重写由随后的ANALYZE操作 做的明确数值数量的估计。n_distinct影响表本身的统计值，而 n_distinct_inherited影响表及其继承子表的统计。 当设置为一个正值时，ANALYZE将会假定列准确包含明确的非空值的指定数目。 当设置为大于或者等于-1的负值时，ANALYZE将会假定在列中的不同的非空值的数目 在表的大小上是线性的；确切的统计将通过乘由给定的数字的绝对值估计的表大小来计。例如， 值-1意味着在此列中的所有值是不同的，值-0.5意味着每个值平均出现两次。 当表的大小随时间变化时这是很有效的，尽管表中行数的乘法运算在查询规划时间之前是不会执行的， 声明一个0值来正常地恢复到估计不同数值的数目。要获取关于使用PostgreSQL 查询优化器做统计的信息，请参阅Section 14.2。
- SET STORAGE
它为一个字段设置存储模式。这个设置控制这个字段是内联保存还是保存在 一个附属的表里，以及数据是否要压缩。PLAIN必需用于定长 的数值(比如integer)并且是内联的、不压缩的。MAIN 用于内联、可压缩的数据。EXTERNAL用于外部保存、不压缩的数据， EXTENDED用于外部的压缩数据。EXTENDED 是大多数支持非PLAIN存储的数据的缺省。使用 EXTERNAL将令在text和bytea字段 上的子字符串操作更快，但付出的代价是增加了存储空间。请注意SET STORAGE 本身并不改变表上的任何东西，只是设置将来的表操作时，建议使用的策略。参阅节 Section 54.2获取更多信息。
- ADD table_constraint
它给表增加一个新的约束，用的语法和CREATE TABLE一样。
- DROP CONSTRAINT [ IF EXISTS ]
它删除一个表上的约束。如果IF EXISTS已被声明 并且不存在约束，那么就不会抛出错误。相反地，在这种情况下会发布一个公告。
- DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
它关闭或者打开属于该表的触发器。一个被关闭掉的触发器是系统仍然知道的， 但是在触发器事件发生的时候不会被执行。对于一个推迟了的触发器，在事件发生的 时候会检查打开状态，而不是在函数实际执行的时候。可以通过指定名字的方法打开 或者关闭任意一个触发器，或者是该表上的所有触发器，或者只是用户触发器(这个 选项排除了那些用于实现外键约束的触发器)。打开或者关闭约束触发器要求超级用 户权限；这么做的时候应该小心，因为如果触发器不执行的话，约束保证的数据完整 性也就没有办法确保了。 触发器启动原理也受配置变量session_replication_role影响。 简单启动的触发器将会在复制任务为"初始"(默认情况)或者"本地"时 启动。配置为 ENABLE REPLICA的触发器将会仅在会话为"replica" 模式时启动，并且配置为ENABLE ALWAYS的触发器将会启动，无论 是否为当前复制模式。
- DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
它配置属于表的重写规则制定。一个不健全的规则对系统来说仍然是可知的， 但在查询重写期间是不被应用的。语义为关闭/启动触发器。这个配置对ON SELECT 规则来说是可忽略的，常常用来保持视图工作，即使当前会话处于一个非默认的复制角色中。
- CLUSTER
它为将来的CLUSTER操作选择默认索引。 实际上并没有重新聚集该表。
- SET WITHOUT CLUSTER
它从表中删除最常用的CLUSTER索引规范。 这影响将来不声明索引的聚集操作。
- SET WITH OIDS
-
它向表中添加一个oid系统列（参阅 Section 5.4）。 如果表已经有OIDs则什么都不做。
请注意这并不等价于ADD COLUMN oid oid； 应当添加一个恰巧名为oid的正常列，而不是系统列。
- SET WITHOUT OIDS
它从表中删除 oid 系统字段。它和 DROP COLUMN oid RESTRICT 完全相同， 只不过是如果表上已经没有 oid 字段的时候不会报错。
- SET ( storage_parameter = value [, ... ] )
-
它为表改变一个或者更多存储参数。参阅 存储参数获取关于可用参数的详细信息。请注意 表的内容将将不会通过此命令被迅速调整；依靠此参数你可能需要重写此表来得到希望的效果。 这可以通过CLUSTER或者爱那个值表重写的ALTER TABLE形式中 的一种来做。
Note: 尽管CREATE TABLE允许OIDS在WITH (storage_parameter)语义中声明， ALTER TABLE但不作为OIDS一个存储参数。相反地， 要使用SET WITH OIDS和SET WITHOUT OIDS形式来 更改OID状态。
- RESET ( storage_parameter [, ... ] )
它重置表的一个或多个存储参数。与SET一样，根据参数的不 同可能需要重写表才能获得想要的效果。
- INHERIT parent_table
-
它将目标表添加为指定父表的新子表。之后在父表上的查询将包含目标表中的 记录。要被添加为一个子表，目标表必须已经包含所有与父表相同的字段(除此之外 当然也可以包含一些其它字段)，这些字段的数据类型必须匹配，并且如果父表的字 段有NOT NULL约束的话子表的相应字段也必须有NOT NULL 约束。
所有父表的CHECK约束必须同时与子表的约束匹配。当前 UNIQUE,PRIMARY KEY,FOREIGN KEY 约束不被考虑在内，但是将来可能会有所改变。
- NO INHERIT parent_table
这种形式从指定父表的子表列表中删除目标表。这样，在父表上的查询将不再目标表中的记录。
- OWNER
这种形式将表、序列、视图的属主改变成指定的用户。
- SET TABLESPACE
。 它更改表的表空间来声明表空间，并为新的表空间删除与表相关的数据文件 表上的索引，若有，不必删除；但可以通过附加的SET TABLESPACE 命令来将他们删除。又见CREATE TABLESPACE。
- RENAME
RENAME形式改变一个表(或者索引、序列、视图)的名字， 或者是表中独立字段的名字。它们对存储的数据没有影响。
- SET SCHEMA
这种形式把表移动到另外一个模式。相关的索引、约束、序列都跟着移动。
除了RENAME和SET SCHEMA之外所有动作都可以 捆绑在一个多次修改列表中并行使用。比如，可以在一个命令里增加几个字段和/或 修改几个字段的类型。对于大表，这么做特别有用，因为只需要对该表做一次处理。
要使用ALTER TABLE，你必须拥有该表。要修改一个表的模式，你还必 须在新模式上拥有CREATE权限。要把该表添加为一个父表的新 子表，你必须同时拥有父表。要修改所有者，你还必须是新的所有角色的直接或间接 成员，并且该成员必须在此表的模式上有CREATE权限。这些限制 强制了修改该所有者不会做任何通过删除和重建表不能做的事情。不过，超级用户可 以以任何方式修改任意表的所有权。
参数
- name
要修改的已有表的名称（可以有模式修饰）。若声明了ONLY， 只有那个表被更改。若未声明ONLY，该表及其所有子表都将会被更改。
- column
现存或新的字段名称
- new_column
现存字段的新名称
- new_name
表的新名称
- type
新字段的类型，或者现存字段的新类型。
- table_constraint
新的表约束定义
- constraint_name
要删除的现有约束的名字
- CASCADE
级联删除依赖于被依赖字段或者约束的对象(比如引用该字段的视图)
- RESTRICT
如果字段或者约束还有任何依赖的对象，则拒绝删除该字段。这是缺省行为。
- trigger_name
要打开或者关闭的单个触发器的名字
- ALL
开启或者关闭所有属于该表的触发器。（如果任何触发器内部产生约束触发器， 这要求超级用户权限，例如那些用于执行外键约束或者可推迟的独特性和排除 约束。）
- USER
关闭或者启动所有属于表的触发器。（如果任何触发器内部产生约束触发器， 这要求超级用户权限，例如那些用于执行外键约束或者可推迟的独特性和排除 约束。）
- index_name
要标记为群集的表上面的索引名字
- storage_parameter
表的存储参数的名字
- value
表的存储参数的新值，根据参数的不同，可能是一个数字或单词。
- parent_table
将要与该表建立/取消关联的父表
- new_owner
该表的新所有者的用户名
- new_tablespace
这个表将要移动到的表空间名字
- new_schema
表将前往的新模式的名字
注意
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 的扩展，它不允许零字段表。