?
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變更一個(gè)現(xiàn)存表的定義。它有好幾種子形式：
- ADD COLUMN
它使用和CREATE TABLE一樣的語(yǔ)法向表中增加一個(gè)新的字段。
- DROP COLUMN [ IF EXISTS ]
它從表中刪除一個(gè)字段。和這個(gè)字段相關(guān)的索引和表約束也會(huì)被自動(dòng)刪除。 如果任何表之外的對(duì)象依賴于這個(gè)字段，必須說(shuō)CASCADE，比如外鍵參考、視圖等等。
- SET DATA TYPE
它改變表中一個(gè)字段的類型。該字段涉及的索引和簡(jiǎn)單的表約束將被自動(dòng)地轉(zhuǎn)換為使用新的字段類型，方法是重新分析最初提供的表達(dá)式。可選的USING子句聲明如何從舊的字段值里計(jì)算新的字段值；如果省略，那么缺省的轉(zhuǎn)換就是從舊類型向新類型的賦值轉(zhuǎn)換。如果從舊數(shù)據(jù)類型到新類型沒(méi)有隱含或者賦值的轉(zhuǎn)換，那么必須提供一個(gè)USING
- SET/DROP DEFAULT
這種形式為一個(gè)字段設(shè)置或者刪除缺省值。請(qǐng)注意缺省值只應(yīng)用于隨后的INSERT命令；它們不會(huì)修改表中已經(jīng)存在的行。也可以為視圖創(chuàng)建缺省，這個(gè)時(shí)候它們是在視圖的ON INSERT規(guī)則應(yīng)用之前插入INSERT語(yǔ)句中去的。
- SET/DROP NOT NULL
它修改一個(gè)字段是否允許 NULL 值或者拒絕 NULL 值。如果表在字段中包含非 NULL ，那么你只可以SET NOT NULL
- SET STATISTICS
-
SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] ) 它設(shè)置或者重置每屬性選項(xiàng)。目前，唯一定義的每屬性選項(xiàng)是n_distinct 和n_distinct_inherited，這些重寫由隨后的ANALYZE操作 做的明確數(shù)值數(shù)量的估計(jì)。n_distinct影響表本身的統(tǒng)計(jì)值，而 n_distinct_inherited影響表及其繼承子表的統(tǒng)計(jì)。 當(dāng)設(shè)置為一個(gè)正值時(shí)，ANALYZE將會(huì)假定列準(zhǔn)確包含明確的非空值的指定數(shù)目。 當(dāng)設(shè)置為大于或者等于-1的負(fù)值時(shí)，ANALYZE將會(huì)假定在列中的不同的非空值的數(shù)目 在表的大小上是線性的；確切的統(tǒng)計(jì)將通過(guò)乘由給定的數(shù)字的絕對(duì)值估計(jì)的表大小來(lái)計(jì)。例如， 值-1意味著在此列中的所有值是不同的，值-0.5意味著每個(gè)值平均出現(xiàn)兩次。 當(dāng)表的大小隨時(shí)間變化時(shí)這是很有效的，盡管表中行數(shù)的乘法運(yùn)算在查詢規(guī)劃時(shí)間之前是不會(huì)執(zhí)行的， 聲明一個(gè)0值來(lái)正常地恢復(fù)到估計(jì)不同數(shù)值的數(shù)目。要獲取關(guān)于使用PostgreSQL 查詢優(yōu)化器做統(tǒng)計(jì)的信息，請(qǐng)參閱Section 14.2。
- SET STORAGE
它為一個(gè)字段設(shè)置存儲(chǔ)模式。這個(gè)設(shè)置控制這個(gè)字段是內(nèi)聯(lián)保存還是保存在 一個(gè)附屬的表里，以及數(shù)據(jù)是否要壓縮。PLAIN必需用于定長(zhǎng) 的數(shù)值(比如integer)并且是內(nèi)聯(lián)的、不壓縮的。MAIN 用于內(nèi)聯(lián)、可壓縮的數(shù)據(jù)。EXTERNAL用于外部保存、不壓縮的數(shù)據(jù)， EXTENDED用于外部的壓縮數(shù)據(jù)。EXTENDED 是大多數(shù)支持非PLAIN存儲(chǔ)的數(shù)據(jù)的缺省。使用 EXTERNAL將令在text和bytea字段 上的子字符串操作更快，但付出的代價(jià)是增加了存儲(chǔ)空間。請(qǐng)注意SET STORAGE 本身并不改變表上的任何東西，只是設(shè)置將來(lái)的表操作時(shí)，建議使用的策略。參閱節(jié) Section 54.2獲取更多信息。
- ADD table_constraint
它給表增加一個(gè)新的約束，用的語(yǔ)法和CREATE TABLE一樣。
- DROP CONSTRAINT [ IF EXISTS ]
它刪除一個(gè)表上的約束。如果IF EXISTS已被聲明 并且不存在約束，那么就不會(huì)拋出錯(cuò)誤。相反地，在這種情況下會(huì)發(fā)布一個(gè)公告。
- DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
它關(guān)閉或者打開屬于該表的觸發(fā)器。一個(gè)被關(guān)閉掉的觸發(fā)器是系統(tǒng)仍然知道的， 但是在觸發(fā)器事件發(fā)生的時(shí)候不會(huì)被執(zhí)行。對(duì)于一個(gè)推遲了的觸發(fā)器，在事件發(fā)生的 時(shí)候會(huì)檢查打開狀態(tài)，而不是在函數(shù)實(shí)際執(zhí)行的時(shí)候。可以通過(guò)指定名字的方法打開 或者關(guān)閉任意一個(gè)觸發(fā)器，或者是該表上的所有觸發(fā)器，或者只是用戶觸發(fā)器(這個(gè) 選項(xiàng)排除了那些用于實(shí)現(xiàn)外鍵約束的觸發(fā)器)。打開或者關(guān)閉約束觸發(fā)器要求超級(jí)用 戶權(quán)限；這么做的時(shí)候應(yīng)該小心，因?yàn)槿绻|發(fā)器不執(zhí)行的話，約束保證的數(shù)據(jù)完整 性也就沒(méi)有辦法確保了。 觸發(fā)器啟動(dòng)原理也受配置變量session_replication_role影響。 簡(jiǎn)單啟動(dòng)的觸發(fā)器將會(huì)在復(fù)制任務(wù)為"初始"(默認(rèn)情況)或者"本地"時(shí) 啟動(dòng)。配置為 ENABLE REPLICA的觸發(fā)器將會(huì)僅在會(huì)話為"replica" 模式時(shí)啟動(dòng)，并且配置為ENABLE ALWAYS的觸發(fā)器將會(huì)啟動(dòng)，無(wú)論 是否為當(dāng)前復(fù)制模式。
- DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
它配置屬于表的重寫規(guī)則制定。一個(gè)不健全的規(guī)則對(duì)系統(tǒng)來(lái)說(shuō)仍然是可知的， 但在查詢重寫期間是不被應(yīng)用的。語(yǔ)義為關(guān)閉/啟動(dòng)觸發(fā)器。這個(gè)配置對(duì)ON SELECT 規(guī)則來(lái)說(shuō)是可忽略的，常常用來(lái)保持視圖工作，即使當(dāng)前會(huì)話處于一個(gè)非默認(rèn)的復(fù)制角色中。
- CLUSTER
它為將來(lái)的CLUSTER操作選擇默認(rèn)索引。 實(shí)際上并沒(méi)有重新聚集該表。
- SET WITHOUT CLUSTER
它從表中刪除最常用的CLUSTER索引規(guī)范。 這影響將來(lái)不聲明索引的聚集操作。
- SET WITH OIDS
-
它向表中添加一個(gè)oid系統(tǒng)列（參閱 Section 5.4）。 如果表已經(jīng)有OIDs則什么都不做。
請(qǐng)注意這并不等價(jià)于ADD COLUMN oid oid； 應(yīng)當(dāng)添加一個(gè)恰巧名為oid的正常列，而不是系統(tǒng)列。
- SET WITHOUT OIDS
它從表中刪除 oid 系統(tǒng)字段。它和 DROP COLUMN oid RESTRICT 完全相同， 只不過(guò)是如果表上已經(jīng)沒(méi)有 oid 字段的時(shí)候不會(huì)報(bào)錯(cuò)。
- SET ( storage_parameter = value [, ... ] )
-
它為表改變一個(gè)或者更多存儲(chǔ)參數(shù)。參閱 存儲(chǔ)參數(shù)獲取關(guān)于可用參數(shù)的詳細(xì)信息。請(qǐng)注意 表的內(nèi)容將將不會(huì)通過(guò)此命令被迅速調(diào)整；依靠此參數(shù)你可能需要重寫此表來(lái)得到希望的效果。 這可以通過(guò)CLUSTER或者愛那個(gè)值表重寫的ALTER TABLE形式中 的一種來(lái)做。
Note: 盡管CREATE TABLE允許OIDS在WITH (storage_parameter)語(yǔ)義中聲明， ALTER TABLE但不作為OIDS一個(gè)存儲(chǔ)參數(shù)。相反地， 要使用SET WITH OIDS和SET WITHOUT OIDS形式來(lái) 更改OID狀態(tài)。
- RESET ( storage_parameter [, ... ] )
它重置表的一個(gè)或多個(gè)存儲(chǔ)參數(shù)。與SET一樣，根據(jù)參數(shù)的不 同可能需要重寫表才能獲得想要的效果。
- INHERIT parent_table
-
它將目標(biāo)表添加為指定父表的新子表。之后在父表上的查詢將包含目標(biāo)表中的 記錄。要被添加為一個(gè)子表，目標(biāo)表必須已經(jīng)包含所有與父表相同的字段(除此之外 當(dāng)然也可以包含一些其它字段)，這些字段的數(shù)據(jù)類型必須匹配，并且如果父表的字 段有NOT NULL約束的話子表的相應(yīng)字段也必須有NOT NULL 約束。
所有父表的CHECK約束必須同時(shí)與子表的約束匹配。當(dāng)前 UNIQUE,PRIMARY KEY,FOREIGN KEY 約束不被考慮在內(nèi)，但是將來(lái)可能會(huì)有所改變。
- NO INHERIT parent_table
這種形式從指定父表的子表列表中刪除目標(biāo)表。這樣，在父表上的查詢將不再目標(biāo)表中的記錄。
- OWNER
這種形式將表、序列、視圖的屬主改變成指定的用戶。
- SET TABLESPACE
。 它更改表的表空間來(lái)聲明表空間，并為新的表空間刪除與表相關(guān)的數(shù)據(jù)文件 表上的索引，若有，不必刪除；但可以通過(guò)附加的SET TABLESPACE 命令來(lái)將他們刪除。又見CREATE TABLESPACE。
- RENAME
RENAME形式改變一個(gè)表(或者索引、序列、視圖)的名字， 或者是表中獨(dú)立字段的名字。它們對(duì)存儲(chǔ)的數(shù)據(jù)沒(méi)有影響。
- SET SCHEMA
這種形式把表移動(dòng)到另外一個(gè)模式。相關(guān)的索引、約束、序列都跟著移動(dòng)。
除了RENAME和SET SCHEMA之外所有動(dòng)作都可以 捆綁在一個(gè)多次修改列表中并行使用。比如，可以在一個(gè)命令里增加幾個(gè)字段和/或 修改幾個(gè)字段的類型。對(duì)于大表，這么做特別有用，因?yàn)橹恍枰獙?duì)該表做一次處理。
要使用ALTER TABLE，你必須擁有該表。要修改一個(gè)表的模式，你還必 須在新模式上擁有CREATE權(quán)限。要把該表添加為一個(gè)父表的新 子表，你必須同時(shí)擁有父表。要修改所有者，你還必須是新的所有角色的直接或間接 成員，并且該成員必須在此表的模式上有CREATE權(quán)限。這些限制 強(qiáng)制了修改該所有者不會(huì)做任何通過(guò)刪除和重建表不能做的事情。不過(guò)，超級(jí)用戶可 以以任何方式修改任意表的所有權(quán)。
參數(shù)
- name
要修改的已有表的名稱（可以有模式修飾）。若聲明了ONLY， 只有那個(gè)表被更改。若未聲明ONLY，該表及其所有子表都將會(huì)被更改。
- column
現(xiàn)存或新的字段名稱
- new_column
現(xiàn)存字段的新名稱
- new_name
表的新名稱
- type
新字段的類型，或者現(xiàn)存字段的新類型。
- table_constraint
新的表約束定義
- constraint_name
要?jiǎng)h除的現(xiàn)有約束的名字
- CASCADE
級(jí)聯(lián)刪除依賴于被依賴字段或者約束的對(duì)象(比如引用該字段的視圖)
- RESTRICT
如果字段或者約束還有任何依賴的對(duì)象，則拒絕刪除該字段。這是缺省行為。
- trigger_name
要打開或者關(guān)閉的單個(gè)觸發(fā)器的名字
- ALL
開啟或者關(guān)閉所有屬于該表的觸發(fā)器。（如果任何觸發(fā)器內(nèi)部產(chǎn)生約束觸發(fā)器， 這要求超級(jí)用戶權(quán)限，例如那些用于執(zhí)行外鍵約束或者可推遲的獨(dú)特性和排除 約束。）
- USER
關(guān)閉或者啟動(dòng)所有屬于表的觸發(fā)器。（如果任何觸發(fā)器內(nèi)部產(chǎn)生約束觸發(fā)器， 這要求超級(jí)用戶權(quán)限，例如那些用于執(zhí)行外鍵約束或者可推遲的獨(dú)特性和排除 約束。）
- index_name
要標(biāo)記為群集的表上面的索引名字
- storage_parameter
表的存儲(chǔ)參數(shù)的名字
- value
表的存儲(chǔ)參數(shù)的新值，根據(jù)參數(shù)的不同，可能是一個(gè)數(shù)字或單詞。
- parent_table
將要與該表建立/取消關(guān)聯(lián)的父表
- new_owner
該表的新所有者的用戶名
- new_tablespace
這個(gè)表將要移動(dòng)到的表空間名字
- new_schema
表將前往的新模式的名字
注意
COLUMN關(guān)鍵字是多余的，可以省略。
如果用ADD COLUMN增加一個(gè)字段，那么所有表中現(xiàn)有行 都初始化為該字段的缺省值(如果沒(méi)有聲明DEFAULT子句，那么就是 NULL)。
通過(guò)非空默認(rèn)值添加一個(gè)列或者改變一個(gè)原有列的類型需要整個(gè)要被重寫的表和索引。 這對(duì)于大型表可能需要大量時(shí)間；這將暫時(shí)需要兩倍的磁盤空間。添加或者刪除一個(gè)系統(tǒng) oid列同樣需要重寫整個(gè)表。
增加一個(gè)CHECK或NOT NULL約束將會(huì)掃描該表以保證 現(xiàn)有的行符合約束要求。
提供在一個(gè)ALTER TABLE里面聲明多個(gè)修改的主要原因是原先需要的 對(duì)表的多次掃描和重寫可以組合成一個(gè)步驟。
DROP COLUMN命令并不是物理上把字段刪除，而只是簡(jiǎn)單地 把它標(biāo)記為對(duì) SQL 操作不可見。隨后對(duì)該表的插入和更新將在該字段存儲(chǔ)一個(gè) NULL 。 因此，刪除一個(gè)字段是很快的，但是它不會(huì)立即釋放表在磁盤上的空間，因?yàn)楸粍h除 了的字段占據(jù)的空間還沒(méi)有回收。這些空間將隨著現(xiàn)有的行的更新而得到回收。 (在刪除系統(tǒng)oid列時(shí)，這些語(yǔ)句不會(huì)應(yīng)用；這是做了一個(gè)直接的重寫。)
SET DATA TYPE要求重寫整個(gè)表的特性有時(shí)候是一個(gè)優(yōu)點(diǎn)，因?yàn)橹貙懙倪^(guò)程 消除了表中沒(méi)用的空間。比如，要想立刻回收被一個(gè)已經(jīng)刪除的字段占據(jù)的空間，最快的 方法是：
ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
這里的anycol是任何在表中還存在的字段，而anytype是和該 字段的原類型一樣的類型。這樣的結(jié)果是在表上沒(méi)有任何可見的語(yǔ)意的變化，但是這個(gè)命 令強(qiáng)迫重寫，這樣就刪除了不再使用的數(shù)據(jù)。
SET DATA TYPE的USING選項(xiàng)實(shí)際上可以聲明涉及該行 舊值的任何表達(dá)式；也就是說(shuō)，它可以引用除了正在被轉(zhuǎn)換的字段之外其它的字段。這樣， 就可以用SET DATA TYPE語(yǔ)法做非常普遍性的轉(zhuǎn)換。因?yàn)檫@個(gè)靈活性， USING表達(dá)式并沒(méi)有作用于該字段的缺省值(如果有的話)；結(jié)果可能 不是缺省表達(dá)式要求的常量表達(dá)式。這就意味著如果從舊類型到新類型沒(méi)有隱含或者賦值 轉(zhuǎn)換的話，那么即使存在USING子句，SET DATA TYPE也 可能無(wú)法把缺省值轉(zhuǎn)換成新的類型。在這種情況下，應(yīng)該用DROP DEFAULT先 刪除缺省，執(zhí)行SET DATA TYPE，然后使用SET DEFAULT增加一個(gè) 合適的新缺省值。類似的考慮也適用于涉及該字段的索引和約束。
如果表有任何后代表，那么如果不在后代表上做同樣的修改的話，就不允許在父表上增加、 重命名、修改一個(gè)字段的類型，也就是說(shuō)，ALTER TABLE ONLY將被拒絕。 這樣就保證了后代表總是有和父表匹配的字段。
一個(gè)遞歸DROP COLUMN操作將只有在后代表并不從任何其它父表中繼 承該字段并且從來(lái)沒(méi)有獨(dú)立定義該字段的時(shí)候才能刪除一個(gè)后代表的字段。一個(gè)非遞歸的 DROP COLUMN(也就是ALTER TABLE ONLY ... DROP COLUMN) 從來(lái)不會(huì)刪除任何后代字段，而是把他們標(biāo)記為獨(dú)立定義的(而不是繼承的)。
TRIGGER,CLUSTER,OWNER,TABLESPACE 行為絕不會(huì)遞歸到后代表；也就是說(shuō)，它們的行為就像總是聲明了ONLY一樣。 添加一個(gè)約束只能在CHECK約束上遞歸。
不允許更改系統(tǒng)表結(jié)構(gòu)的任何部分。
請(qǐng)參考CREATE TABLE部分獲取更多有效參數(shù)的描述。章 Chapter 5里有更多有關(guān)繼承的信息。
Examples
向表中增加一個(gè)varchar列：
ALTER TABLE distributors ADD COLUMN address varchar(30);
從表中刪除一個(gè)字段：
ALTER TABLE distributors DROP COLUMN address RESTRICT;
在一個(gè)操作中修改兩個(gè)現(xiàn)有字段的類型：
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);
使用一個(gè)USING子句，把一個(gè)包含 UNIX 時(shí)間戳的 integer 字段轉(zhuǎn)化成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';
同樣地，當(dāng)字段有一個(gè)不會(huì)自動(dòng)轉(zhuǎn)換成新類型的缺省值表達(dá)式時(shí)：
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();
對(duì)現(xiàn)存字段改名：
ALTER TABLE distributors RENAME COLUMN address TO city;
更改現(xiàn)存表的名字：
ALTER TABLE distributors RENAME TO suppliers;
給一個(gè)字段增加一個(gè)非空約束：
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
從一個(gè)字段里刪除一個(gè)非空約束：
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
給一個(gè)表增加一個(gè)檢查約束：
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
刪除一個(gè)表及其所有子表的監(jiān)查約束
ALTER TABLE distributors DROP CONSTRAINT zipchk;
從表中刪除一個(gè)檢查約束只需要：
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
(此檢查約束僅存在于子表中。)
向表中增加一個(gè)外鍵約束：
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
給表增加一個(gè)(多字段)唯一約束：
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
給一個(gè)表增加一個(gè)自動(dòng)命名的主鍵約束，要注意的是一個(gè)表只能有一個(gè)主鍵：
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
把表移動(dòng)到另外一個(gè)表空間：
ALTER TABLE distributors SET TABLESPACE fasttablespace;
把表移動(dòng)到另外一個(gè)模式：
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
兼容性
ADD, DROP, SET DEFAULT形式與 SQL 標(biāo)準(zhǔn)兼容。其它形式是PostgreSQL對(duì) SQL 標(biāo)準(zhǔn) 的擴(kuò)展。還有，在一個(gè)ALTER TABLE命令里聲明多個(gè)操作也是擴(kuò)展。
ALTER TABLE DROP COLUMN可以用于刪除表中的唯一的一個(gè)字段，留下 一個(gè)零字段的表。這是對(duì) SQL 的擴(kuò)展，它不允許零字段表。