PostgreSQL 中文文檔

ALTER TABLE

Name

ALTER TABLE -- 修改表的定義

Synopsis

ALTER TABLE [ ONLY ] name [ * ]    action [, ... ]ALTER TABLE [ ONLY ] name [ * ]    RENAME [ COLUMN ] column TO new_columnALTER TABLE name    RENAME TO new_nameALTER TABLE name    SET SCHEMA new_schemawhere 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 的擴充，它不允許零欄位表。