ALTER TABLE — テーブル定義を変更する
ALTER TABLE [ IF EXISTS ] [ ONLY ]name
[ * ]action
[, ... ] ALTER TABLE [ IF EXISTS ] [ ONLY ]name
[ * ] RENAME [ COLUMN ]column_name
TOnew_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ]name
[ * ] RENAME CONSTRAINTconstraint_name
TOnew_constraint_name
ALTER TABLE [ IF EXISTS ]name
RENAME TOnew_name
ALTER TABLE [ IF EXISTS ]name
SET SCHEMAnew_schema
ALTER TABLE ALL IN TABLESPACEname
[ OWNED BYrole_name
[, ... ] ] SET TABLESPACEnew_tablespace
[ NOWAIT ] ALTER TABLE [ IF EXISTS ]name
ATTACH PARTITIONpartition_name
{ FOR VALUESpartition_bound_spec
| DEFAULT } ALTER TABLE [ IF EXISTS ]name
DETACH PARTITIONpartition_name
ここで、action
は以下のいずれかです。 ADD [ COLUMN ] [ IF NOT EXISTS ]column_name
data_type
[ COLLATEcollation
] [column_constraint
[ ... ] ] DROP [ COLUMN ] [ IF EXISTS ]column_name
[ RESTRICT | CASCADE ] ALTER [ COLUMN ]column_name
[ SET DATA ] TYPEdata_type
[ COLLATEcollation
] [ USINGexpression
] ALTER [ COLUMN ]column_name
SET DEFAULTexpression
ALTER [ COLUMN ]column_name
DROP DEFAULT ALTER [ COLUMN ]column_name
{ SET | DROP } NOT NULL ALTER [ COLUMN ]column_name
DROP EXPRESSION [ IF EXISTS ] ALTER [ COLUMN ]column_name
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ (sequence_options
) ] ALTER [ COLUMN ]column_name
{ SET GENERATED { ALWAYS | BY DEFAULT } | SETsequence_option
| RESTART [ [ WITH ]restart
] } [...] ALTER [ COLUMN ]column_name
DROP IDENTITY [ IF EXISTS ] ALTER [ COLUMN ]column_name
SET STATISTICSinteger
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 } ADDtable_constraint
[ NOT VALID ] ADDtable_constraint_using_index
ALTER CONSTRAINTconstraint_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINTconstraint_name
DROP CONSTRAINT [ IF EXISTS ]constraint_name
[ RESTRICT | CASCADE ] DISABLE TRIGGER [trigger_name
| ALL | USER ] ENABLE TRIGGER [trigger_name
| ALL | USER ] ENABLE REPLICA TRIGGERtrigger_name
ENABLE ALWAYS TRIGGERtrigger_name
DISABLE RULErewrite_rule_name
ENABLE RULErewrite_rule_name
ENABLE REPLICA RULErewrite_rule_name
ENABLE ALWAYS RULErewrite_rule_name
DISABLE ROW LEVEL SECURITY ENABLE ROW LEVEL SECURITY FORCE ROW LEVEL SECURITY NO FORCE ROW LEVEL SECURITY CLUSTER ONindex_name
SET WITHOUT CLUSTER SET WITHOUT OIDS SET TABLESPACEnew_tablespace
SET { LOGGED | UNLOGGED } SET (storage_parameter
[=value
] [, ... ] ) RESET (storage_parameter
[, ... ] ) INHERITparent_table
NO INHERITparent_table
OFtype_name
NOT OF OWNER TO {new_owner
| CURRENT_USER | SESSION_USER } REPLICA IDENTITY { DEFAULT | USING INDEXindex_name
| FULL | NOTHING } また、partition_bound_spec
は以下のいずれかです。 IN (partition_bound_expr
[, ...] ) | FROM ( {partition_bound_expr
| MINVALUE | MAXVALUE } [, ...] ) TO ( {partition_bound_expr
| MINVALUE | MAXVALUE } [, ...] ) | WITH ( MODULUSnumeric_literal
, REMAINDERnumeric_literal
) また、column_constraint
は以下の通りです。 [ CONSTRAINTconstraint_name
] { NOT NULL | NULL | CHECK (expression
) [ NO INHERIT ] | DEFAULTdefault_expr
| GENERATED ALWAYS AS (generation_expr
) STORED | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ (sequence_options
) ] | UNIQUEindex_parameters
| PRIMARY KEYindex_parameters
| REFERENCESreftable
[ (refcolumn
) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETEreferential_action
] [ ON UPDATEreferential_action
] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] また、table_constraint
は以下の通りです。 [ CONSTRAINTconstraint_name
] { CHECK (expression
) [ NO INHERIT ] | UNIQUE (column_name
[, ... ] )index_parameters
| PRIMARY KEY (column_name
[, ... ] )index_parameters
| EXCLUDE [ USINGindex_method
] (exclude_element
WITHoperator
[, ... ] )index_parameters
[ WHERE (predicate
) ] | FOREIGN KEY (column_name
[, ... ] ) REFERENCESreftable
[ (refcolumn
[, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETEreferential_action
] [ ON UPDATEreferential_action
] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] またtable_constraint_using_index
は以下の通りです。 [ CONSTRAINTconstraint_name
] { UNIQUE | PRIMARY KEY } USING INDEXindex_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]UNIQUE
、PRIMARY KEY
、および、EXCLUDE
制約でのindex_parameters
は以下の通りです。 [ INCLUDE (column_name
[, ... ] ) ] [ WITH (storage_parameter
[=value
] [, ... ] ) ] [ USING INDEX TABLESPACEtablespace_name
]EXCLUDE
制約でのexclude_element
は以下の通りです。 {column_name
| (expression
) } [opclass
] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
ALTER TABLE
は既存のテーブルの定義を変更します。
以下のようにいくつかの副構文があります。
要求されるロックレベルはそれぞれの副構文によって異なることに注意してください。
特に記述がなければACCESS EXCLUSIVE
ロックを取得します。
複数のサブコマンドが使われるときは、それらのサブコマンドが要求するうち、もっとも高いレベルのロックを取得します。
ADD COLUMN [ IF NOT EXISTS ]
この構文を使用すると、CREATE TABLEと同じ構文を使って新しい列をテーブルに追加できます。
IF NOT EXISTS
が指定され、その名前の列が既に存在している場合は、エラーが発生しません。
DROP COLUMN [ IF EXISTS ]
この構文を使用すると、テーブルから列を削除できます。
削除する列を含んでいるインデックスおよびテーブル制約も自動的に削除されます。
削除する列を参照する多変量統計がある場合、列の削除の結果、その統計が1つの列のデータしか含まないようになるなら、それも削除されます。
また、削除する列にテーブル以外が依存(例えば、外部キー制約、ビューなど)している場合、CASCADE
を付ける必要があります。
IF EXISTS
が指定されている場合、もしその列がなかったとしてもエラーにはなりません。
この場合は代わりに注意が出力されます。
SET DATA TYPE
この構文を使用すると、テーブルの列の型を変更できます。
その列を含むインデックスと簡単なテーブル制約は、元々与えられた式を再解析し、新しい型を使用するように自動的に変換されます。
COLLATE
句を使うと、新しい列の照合順を指定できます。
省略時の照合順は新しい列の型のデフォルトになります。
USING
句を使うと、古い列値をどのように新しい値に計算するかを指定できます。
省略された場合、デフォルトの変換は、古いデータ型から新しいデータ型への代入キャストと同じになります。
古いデータ型から新しいデータ型への暗黙キャストあるいは代入キャストがない場合、USING
句を指定しなければなりません。
SET
/DROP DEFAULT
これらの構文を使用すると、列のデフォルト値を設定または削除できます(ここで、削除はデフォルト値をNULLに設定することと等価です)。
新しいデフォルト値は、変更後に行われるINSERT
またはUPDATE
コマンドにのみ適用されます。
テーブル内の既存の行は変更されません。
SET
/DROP NOT NULL
これらの構文は、列の値としてNULL値を認めるか拒絶するかを変更します。
SET NOT NULL
は、テーブルの項目でその列がNULL
値であるものが1つもない場合にのみ、その列に設定可能です。
通常これはALTER TABLE
がテーブル全体をスキャンする際に確認されます。しかしながら、NULL
が存在できないことを示す有効なCHECK
制約が見つかれば、テーブルスキャンは省略されます。
このテーブルがパーティションの場合、親テーブルでNOT NULL
の印がつけられている列についてDROP NOT NULL
を実行することはできません。
すべてのパーティションからNOT NULL
制約を削除するには、親テーブルでDROP NOT NULL
を実行してください。
親テーブルにNOT NULL
制約がない場合でも、望むなら各パーティションにそのような制約を追加することができます。
つまり、親テーブルがNULLを許していても子テーブルでNULLを禁止することができますが、その逆はできません。
DROP EXPRESSION [ IF EXISTS ]
この構文は、格納された生成列を通常の基本列に変換します。 列の既存のデータは保持されますが、以後の変更はもはや生成式を適用しません。
DROP EXPRESSION IF EXISTS
が指定され、その列が格納された生成列でない場合は、エラーを発生させません。
この場合、注意メッセージが発行されます。
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
SET GENERATED { ALWAYS | BY DEFAULT }
DROP IDENTITY [ IF EXISTS ]
この構文では、列がIDENTITY列であるかどうか、または既存のIDENTITY列の生成属性を変更することができます。
詳細はCREATE TABLEを参照してください。
SET DEFAULT
同様に、この構文は、変更後に行われるINSERT
またはUPDATE
コマンドにのみ適用されます。
テーブル内の既存の行は変更されません。
DROP IDENTITY IF EXISTS
が指定され、その列がIDENTITY列でない場合は、エラーを発生させません。
この場合、注意メッセージが発行されます。
SET sequence_option
RESTART
この構文では、既存のIDENTITY列に紐付けられているシーケンスを変更します。
sequence_option
はINCREMENT BY
などALTER SEQUENCEがサポートするオプションです。
SET STATISTICS
この構文は、以後のANALYZE操作において、列単位での統計情報収集対象を設定します。 対象として、0から10000までの範囲の値を設定可能です。 また、対象を-1に設定すると、システムのデフォルト統計情報対象(default_statistics_target)が使用されます。 PostgreSQLの問い合わせプランナによる統計情報の使用に関する詳細は、14.2を参照してください。
SET STATISTICS
はSHARE UPDATE EXCLUSIVE
ロックを取得します。
SET ( attribute_option
= value
[, ... ] )
RESET ( attribute_option
[, ... ] )
この構文は属性単位のオプションの設定または設定解除を行います。
現時点では属性単位のオプションで定義されているのはn_distinct
およびn_distinct_inherited
のみです。
これらのオプションは、その後のANALYZE操作により生成される個別値数の推定値を上書きします。
n_distinct
はテーブル自身の統計情報に影響を与え、n_distinct_inherited
はテーブルとそれを継承した子テーブルから集めた統計情報に影響を与えます。
正の値の場合、ANALYZE
は、その列には、正確に指定された個数の非NULLの個別値が存在するものとみなします。
負の値の場合、この値は-1以上でなければなりませんが、ANALYZE
は、その列内の非NULLの個別値はテーブルのサイズに線形であるとみなし、推定テーブルサイズに指定した値の絶対値を乗じた値が個別値数であるとみなします。
たとえば、-1という値は、列内のすべての値に重複がないことを意味し、-0.5という値は個々の値は平均して2回現れることを意味します。
テーブルの行数との乗算は問い合わせ計画を作成するまで行われませんので、テーブルサイズが変わり続けるような場合にこれは有用かもしれません。
0という値を指定することで、個別値数の推定を通常に戻します。
PostgreSQL問い合わせプランナにおける統計情報の使用に関しては14.2を参照してください。
属性単位のオプションの変更はSHARE UPDATE EXCLUSIVE
ロックを取得します。
SET STORAGE
この構文は、列の保管モードを設定します。
列をインラインで保持するか補助TOASTテーブルに保持するか、また、データを圧縮するかどうかを制御できます。
PLAIN
は、integer
のような固定長の値に対して使用します。インラインで保持され、圧縮されません。
MAIN
は、インラインで保持されていて、圧縮可能なデータに使用します。
EXTERNAL
は圧縮されていない外部データに使用します。
EXTENDED
は圧縮された外部データに使用します。
EXTENDED
は、PLAIN
以外の保管をサポートするほとんどのデータ型におけるデフォルトです。
EXTERNAL
を使用すると、非常に長いtext
およびbytea
列に対する部分文字列操作の処理速度が向上しますが、必要な保管容量が増えるというデメリットがあります。
SET STORAGE
自体はテーブルをまったく変更しないことに注意してください。
以後のテーブルの更新時に遂行する戦略を設定するだけです。
詳細は68.2を参照してください。
ADD table_constraint
[ NOT VALID ]
この構文は、CREATE TABLEと同じ制約構文に加え、現時点では外部キー制約と検査制約でのみ許されるNOT VALID
オプションを使って新しい制約をテーブルに追加します。
通常この構文は、テーブルの既存の行が新しい制約を満たすか確認するため、テーブルのスキャンの原因となります。
しかし、NOT VALID
オプションが使われていれば、時間がかかるかもしれないこのスキャンは省略されます。
それでも、制約はその後の挿入や更新に対して強制されます(つまり、外部キー制約の場合、被参照テーブルに一致する行が存在しない限り失敗します。指定された検査制約に一致する新しい行が存在しない限り失敗します)。
しかしデータベースは、VALIDATE CONSTRAINT
オプションを使用して検証されるまで、テーブル内のすべての行で制約が保持されているとみなしません。
NOT VALID
オブションを使うことに関する更なる情報はNotes以下を参照してください。
ADD
のほとんどの構文ではtable_constraint
ACCESS EXCLUSIVE
ロックが必要ですが、ADD FOREIGN KEY
ではSHARE ROW EXCLUSIVE
ロックだけが必要です。
ADD FOREIGN KEY
は、制約を宣言したテーブルでのロックに加えて、被参照テーブルのSHARE ROW EXCLUSIVE
ロックも取得することに注意してください。
一意性制約や主キー制約がパーティションテーブルに追加されるときには、追加的な制限が適用されます。
CREATE TABLEを参照してください。
また、今のところ、パーティションテーブルでの外部キー制約ではNOT VALID
と宣言できません。
ADD table_constraint_using_index
この構文は、既存の一意性インデックスに基づき、テーブルにPRIMARY KEY
またはUNIQUE
制約を新たに追加します。
インデックスのすべての列がこの制約に含まれます。
このインデックスは式列を持つことはできず、また部分インデックスであってはいけません。
またこれはデフォルトのソート順序を持つB-Treeインデックスでなければなりません。
これらの制限により、このインデックスが通常のADD PRIMARY KEY
またはADD UNIQUE
コマンドにより構築されたインデックスと等価であることを確実にします。
PRIMARY KEY
が指定され、インデックスの列がNOT NULL
と印付けされていない場合、このコマンドはこうした列のそれぞれに対してALTER COLUMN SET NOT NULL
の実施を試みます。
これは列にNULLが含まれないことを検証するために完全なテーブルスキャンを必要とします。
この他の場合においては、これが高速な操作です。
制約名が提供された場合、インデックスの名前は制約名に合うように変更されます。 提供されない場合は制約にはインデックスと同じ名前が付けられます。
このコマンドの実行後、インデックスは、制約により「所有」され、それはインデックスが通常のADD PRIMARY KEY
またはADD UNIQUE
により構築された場合と同様です。
特にこの制約を削除するとインデックスも消えてしまいます。
この形式は今のところパーティションテーブルではサポートされません。
既存のインデックスを使用した制約の追加は、テーブル更新を長時間ブロックすることなく新しい制約を追加しなければならない場合に有用になる可能性があります。
このためには、CREATE INDEX CONCURRENTLY
を用いてインデックスを作成し、この構文を使用して正式の制約としてインストールしてください。
後述の例を参照してください。
ALTER CONSTRAINT
この構文は以前に作成された制約の属性を変更します。 現在は外部キー制約のみを変更できます。
VALIDATE CONSTRAINT
この構文は、以前にNOT VALID
として作成された外部キー制約または検査制約を、これらの制約を満たさない行が存在しないことを確認するためにテーブルをスキャンして、検証します。
制約がすでに有効であると記録されている場合は何も起こりません。
(このコマンドの有用性の説明はNotes以下を参照してください。)
DROP CONSTRAINT [ IF EXISTS ]
この構文はテーブル上の指定した制約を、制約の基となるインデックスと共に削除します。
IF EXISTS
が指定された場合、その制約がなくてもエラーになりません。
この場合は代わりに注意が出力されます。
DISABLE
/ENABLE [ REPLICA | ALWAYS ] TRIGGER
この構文を使用すると、テーブルに属するトリガの発行について設定することができます。 無効にされたトリガはシステム上に存在し続けますが、トリガイベントが発生したとしても実行されません。 遅延トリガの場合、有効無効状態の確認は、トリガ関数を実際に実行しようとする時ではなく、イベントの発生時に行われます。 名前でトリガを1つ指定して有効または無効にすることもできますし、テーブル上のすべてのトリガを有効または無効にすることもできます。 また、ユーザトリガのみを有効または無効にすることも可能です (このオプションは、外部キー制約、遅延可能な一意性および排他制約を実装するために使用される内部向けに生成される制約トリガを除外します。) 内部向けに生成される制約トリガを有効または無効にするにはスーパーユーザ権限が必要です。 トリガが実行されなかった場合は当然ながら制約の整合性が保証されませんので、制約トリガの無効化は注意して実行しなければなりません。
トリガ発行機構は設定変数session_replication_roleの影響も受けます。
単に有効としたトリガ(デフォルト)では、レプリケーションロールが「origin」(デフォルト)または「local」の場合に発行されます。
ENABLE REPLICA
と設定されたトリガでは、セッションが「replica」モードである場合のみ発行されます。
そして、ENABLE ALWAYS
と設定されたトリガでは、現在のレプリケーションロールに関係なく発行されます。
この仕組みの効果はデフォルト設定ではレプリカ上でトリガが発行しないことです。
トリガがオリジンでテーブル間でデータを伝播するのに使われている場合にレプリケーションシステムは伝播したデータもレプリケーションします。レプリカ上でトリガが再度発動すべきではありませんので、これは有用です。
しかしながら、トリガが外部的な警告を発するなどの他の意図で使われている場合、レプリカでもトリガが発行されるようにENABLE ALWAYS
を設定するのが適切と言えます。
このコマンドはSHARE ROW EXCLUSIVE
を取得します。
DISABLE
/ENABLE [ REPLICA | ALWAYS ] RULE
この構文を使用すると、テーブルに属する書き換えルールの実行について設定することができます。
ルールは無効にしてもシステムに残りますが、問い合わせ書き換え時に適用されません。
この意味はトリガの有効化、無効化と同じです。
この設定はON SELECT
ルールでは無視されます。
現在のセッションがデフォルト以外のレプリケーションモードであったとしても、ビュー操作を維持するために常に適用されます。
前述のトリガと同様に、ルール発行機構は設定変数session_replication_roleの影響を受けます。
DISABLE
/ENABLE ROW LEVEL SECURITY
これらの構文は、テーブルの行セキュリティポリシーの適用を制御します。 有効にされ、かつテーブルにポリシーが存在しない場合は、デフォルトの拒絶ポリシーが適用されます。 行単位セキュリティが無効になっている場合でも、テーブルのセキュリティが存在し得ることに注意してください。 この場合、ポリシーは適用されず、無視されます。 CREATE POLICYも参照してください。
NO FORCE
/FORCE ROW LEVEL SECURITY
これらの構文は、ユーザがテーブルの所有者である場合について、テーブルの行セキュリティポリシーの適用を制御します。 有効の場合、ユーザがテーブルの所有者であれば、行セキュリティポリシーが適用されます。 無効(デフォルト)の場合、ユーザがテーブルの所有者であれば、行セキュリティポリシーは適用されません。 CREATE POLICYも参照してください。
CLUSTER ON
この構文は、以後のCLUSTER操作用のデフォルトインデックスを選択します。 テーブルの再クラスタ化は実際には行いません。
clusterオプションの変更はSHARE UPDATE EXCLUSIVE
ロックを取得します。
SET WITHOUT CLUSTER
この構文は、テーブルから、一番最後に適用されたCLUSTERインデックス指定を削除します。 以後のインデックスを指定しないクラスタ操作に影響を及ぼします。
clusterオプションの変更はSHARE UPDATE EXCLUSIVE
ロックを取得します。
SET WITHOUT OIDS
システム列oid
を削除する、後方互換のための構文です。
システム列oid
は今では追加できませんので、これは効果がありません。
SET TABLESPACE
この構文を使用すると、テーブルのテーブル空間を指定したテーブル空間に変更し、テーブルに関連するデータファイルを新しいテーブル空間に移動することができます。
テーブルにインデックスがあっても移動されません。
インデックスを移動するには、別途SET TABLESPACE
コマンドを実行します。
パーティションテーブルに適用された場合には何も移動されませんが、以後CREATE TABLE PARTITION OF
で作られるパーティションは、TABLESPACE
句により上書きされない限り、そのテーブル空間を使うようになります。
ALL IN TABLESPACE
構文を使うことで、テーブル空間内の現在のデータベースのすべてのテーブルを移動することができます。
この場合、移動されるすべてのテーブルがまずロックされ、それから一つずつ移動されます。
この構文はOWNED BY
もサポートしており、これを使うと、指定のロールが所有しているテーブルだけを移動します。
NOWAIT
を指定した場合、必要とするすべてのロックを即座に獲得できなければ、このコマンドは失敗します。
このコマンドではシステムカタログは移動されないことに注意し、必要なら代わりにALTER DATABASE
を使うか、あるいはALTER TABLE
で明示的に指定してください。
information_schema
のリレーションはシステムカタログとはみなされないので、移動されます。
CREATE TABLESPACEも参照してください。
SET { LOGGED | UNLOGGED }
この構文は、テーブルをログを取らないテーブルからログを取るテーブルに変更、あるいはその逆を行います(UNLOGGED
参照)。
これは一時テーブルに対して使うことはできません。
SET ( storage_parameter
[= value
] [, ... ] )
この構文は、1つ以上のテーブルの格納パラメータを変更します。
設定可能なパラメータの詳細に関してはCREATE TABLE文書のStorage Parametersを参照してください。
このコマンドによってテーブルの内容が即座に変更されない点に注意してください。
パラメータによりますが、期待する効果を得るためにテーブルを書き換える必要がある場合があります。
このためには、VACUUM FULL、CLUSTERまたはテーブルを強制的に書き換えるALTER TABLE
の構文のいずれかを使用してください。
プランナに関連するパラメータについては、次にテーブルがロックされた時に変更が有効になるため、現在実行中の問い合わせは影響を受けません。
fillfactor、TOAST、およびautovacuumのストレージパラメータおよびプランナに関連するパラメータparallel_workers
についてはSHARE UPDATE EXCLUSIVE
ロックが獲得されます。
RESET ( storage_parameter
[, ... ] )
この構文は、1つ以上の格納パラメータをデフォルト値に再設定します。
SET
同様、テーブル全体を更新するためにテーブルの書き換えが必要になる場合があります。
INHERIT parent_table
この構文は、対象テーブルを指定した親テーブルの子テーブルとして追加します。
その後に行われる親テーブルへの問い合わせには対象テーブルの項目も含まれます。
子テーブルとして追加するためには、対象テーブルには親テーブルと同じ列がすべて含まれていなければなりません。
(この他の列を持つこともできます。)
これらの列のデータ型は一致している必要があり、親テーブルでNOT NULL
制約がある場合は、子テーブルでも同様にNOT NULL
制約を持たなければなりません。
また、親テーブルのCHECK
制約すべてについても、一致する制約が子テーブルに存在しなければなりません。
ただし、親テーブルにおいて継承不可と印付けされている(つまりALTER TABLE ... ADD CONSTRAINT ... NO INHERIT
付きで作成された)制約は除きます(これらは無視されます)。
一致する子テーブルの制約はすべて継承不可であってはなりません。
現時点ではUNIQUE
、PRIMARY KEY
、FOREIGN KEY
制約は無視されますが、将来変更されるかもしれません。
NO INHERIT parent_table
この構文は、指定した親テーブルの子テーブル群から対象のテーブルを削除します。 親テーブルへの問い合わせでは、対象としたテーブルからのデータが含まれなくなります。
OF type_name
この構文は、CREATE TABLE OF
で形成されたかのように、テーブルと複合型とを関連付けします。
テーブルの列名とその型のリストは、複合型のものと正確に一致していなければなりません。
テーブルはどのテーブルも継承していてはいけません。
これらの制限によりCREATE TABLE OF
により作成できるテーブル定義と同等になります。
NOT OF
この構文は型と型付けされたテーブルの関連を取り除きます。
OWNER TO
この構文を使用すると、テーブル、シーケンス、ビュー、マテリアライズドビュー、または外部テーブルの所有者を、指定したユーザに変更できます。
REPLICA IDENTITY
この構文を使用すると、更新あるいは削除された行を特定できるよう、先行書き込みログに書き込まれる情報を変更します。
このオプションは、論理レプリケーションが使われている場合以外は何の効果もありません。
DEFAULT
はシステムテーブル以外についてのデフォルトで、主キー列があれば、その古い値を記録します。
USING INDEX
は指定したインデックスに含まれる列の古い値を記録しますが、このインデックスは一意であり、部分インデックスや遅延可能インデックスではなく、またNOT NULL
の列のみを含まなければなりません。
FULL
は行のすべての列の古い値を記録します。
NOTHING
は古い行の情報を何も記録しません(これがシステムテーブルについてのデフォルトです)。
どの場合についても、行の古いバージョンと新しいバージョンの間で、ログに記録される列のうち少なくとも1つが変わっていなければ、古い値はログに記録されません。
RENAME
RENAME
構文を使用すると、テーブル(もしくは、インデックス、シーケンス、ビュー、マテリアライズドビュー、外部テーブル)の名前、テーブルの個々の列名、テーブルの制約名を変更できます。
元となるインデックスを持つ制約名を変更するとき、インデックス名も同様に変更されます。
格納されているデータへの影響はありません。
SET SCHEMA
この構文を使用して、テーブルを別のスキーマに移動することができます。 関連するインデックスや制約、テーブル列により所有されるシーケンスも同様に移動されます。
ATTACH PARTITION partition_name
{ FOR VALUES partition_bound_spec
| DEFAULT }
この構文は、既存のテーブル(それ自体がパーティションテーブルのこともあります)を対象テーブルのパーティションとして追加します。
テーブルは、FOR VALUES
を使って指定の値のパーティションとして、あるいは、DEFAULT
を使ってデフォルトパーティションとして追加できます。
対象テーブルの各インデックスについて、対応するインデックスが付加されるテーブルに作られます。
また、同等のインデックスが既にある場合には、そのインデックスが、ALTER INDEX ATTACH PARTITION
が実行された場合と同様に、対象テーブルのインデックスに付加されます。
既存のテーブルが外部テーブルの場合、今のところ対象テーブルにUNIQUE
インデックスがあるときにはテーブルを対象テーブルのパーティションとして追加することはできない点に注意してください(CREATE FOREIGN TABLEも参照してください)。
対象テーブルにある各ユーザ定義の行レベルのトリガに対しては、対応するものが付加されるテーブルに作られます。
FOR VALUES
を使ったパーティションはpartition_bound_spec
でCREATE TABLEと同じ構文を使います。
パーティション境界の指定は、対象テーブルのパーティション戦略とパーティションキーと対応していなければなりません。
付加されるテーブルは、対象と全て同じ列を持ち、それ以上の列は持たず、列の型も一致していなければなりません。
また、対象テーブルにある全てのNOT NULL
およびCHECK
制約を持たなければなりません。
今のところ、FOREIGN KEY
制約は考慮されません。
親テーブルのUNIQUE
およびPRIMARY KEY
制約は、既に在るのでなければ、パーティションに作られます。
もし、アタッチされるテーブルのいずれかのCHECK
制約がNO INHERIT
と印付けされていたなら、コマンドは失敗します。
このような制約はNO INHERIT
句なしに再作成しなければなりません。
新しいパーティションが通常のテーブルの場合、テーブルに存在する行がパーティションの制約に違反しないことを確認するため、テーブルの全件走査が行われます。
このコマンドを実行するより前に、望まれるパーティションの制約を満たす行だけしか許さないような有効なCHECK
制約をテーブルに追加すれば、この全件走査を避けることができます。
CHECK
制約は、パーティションの制約を確認するためにテーブルをスキャンする必要がないか決めるために使われます。
しかし、パーティションキーに式が一つでもあり、パーティションがNULL
値を受け付けないときは、この仕組みは機能しません。
NULL
値を受け付けないリストパーティションに追加するときも、それが式でないなら、パーティションキーの列にNOT NULL
制約を追加してください。
新しいパーティションが外部テーブルの場合、外部テーブルのすべての行がパーティションの制約に従うかどうかの確認は何も行われません。 (外部テーブルの制約についてはCREATE FOREIGN TABLEの議論を参照してください。)
テーブルがデフォルトパーティションを持っている場合、新たなパーティションの定義はデフォルトパーティションに対するパーティション制約を変更します。
デフォルトパーティションは新パーティションに移動すべきいかなる行を含むことができず、そのような行が無いことを確認するためスキャンが行われます。
このスキャンは、新パーティションのスキャンと同様に、適切なCHECK
制約があれば回避できます。
やはり、新パーティションのスキャンと同様に、デフォルトパーティションが外部テーブルであるときは、このスキャンは常に省略されます。
パーティションの追加は、追加されるテーブルと(もしあれば)デフォルトパーティションでのACCESS EXCLUSIVE
ロックに加えて、親テーブルでSHARE UPDATE EXCLUSIVE
ロックを取得します。
DETACH PARTITION
partition_name
この構文は、指定したパーティションを対象のテーブルから切り離します。 切り離されたパーティションは単独のテーブルとして存在し続けますが、切り離される前のテーブルとの紐付けはなくなります。 対象テーブルのインデックスに付加されていた全てのインデックスも切り離されます。 対象テーブルのものの複製として作られたトリガは削除されます。
RENAME
、SET SCHEMA
、ATTACH PARTITION
、DETACH PARTITION
は、複数の変更リストに結合して、まとめて処理することができますが、それらを除き、ALTER TABLEのすべての構文は1つだけのテーブルに対して作用します。
例えば、複数の列の追加、型の変更を単一のコマンドで実行することができます。
これは特に巨大なテーブルでは便利です。変更のために必要なテーブル全体の走査が1回で済むからです。
ALTER TABLE
コマンドを使用するには、変更するテーブルを所有している必要があります。
テーブルのスキーマあるいはテーブル空間を変更するには、新しいスキーマあるいはテーブル空間におけるCREATE
権限も持っていなければなりません。
テーブルを親テーブルの新しい子テーブルとして追加するには、親テーブルも所有している必要があります。
またテーブルをテーブルのパーティションとして追加する場合、追加されるテーブルを所有している必要があります。
また、所有者を変更するには、新しい所有ロールの直接あるいは間接的なメンバでなければならず、かつ、そのロールがテーブルのスキーマにおけるCREATE
権限を持たなければなりません
(この制限により、テーブルの削除と再作成を行ってもできないことが、所有者の変更によってもできないようにしています。
ただし、スーパーユーザはすべてのテーブルの所有者を変更することができます)。
列の追加、列の型の変更、OF
句の使用を行うためには、データ型に対するUSAGE
権限を持たなければなりません。
IF EXISTS
テーブルが存在しない場合でもエラーとしません。 この場合は注意メッセージが発行されます。
name
変更対象となる既存のテーブルの名前です(スキーマ修飾名も可)。
テーブル名の前にONLY
が指定された場合、そのテーブルのみが変更されます。
ONLY
が指定されていない場合、そのテーブルおよび(もしあれば)そのテーブルを継承する全てのテーブルが更新されます。
オプションで、テーブル名の後に*
を指定することで、明示的に継承するテーブルも含まれることを示すことができます。
column_name
新規または既存の列の名前です。
new_column_name
既存の列の新しい名前です。
new_name
テーブルの新しい名前です。
data_type
新しい列のデータ型、もしくは既存の列に対する新しいデータ型です。
table_constraint
テーブルの新しいテーブル制約です。
constraint_name
新しい、あるいは既存の制約の名前です。
CASCADE
削除された列や制約に依存しているオブジェクト(例えば、削除された列を参照しているビューなど)を自動的に削除し、さらにそれらのオブジェクトに依存するすべてのオブジェクトも削除します(5.14参照)。
RESTRICT
依存しているオブジェクトがある場合、列または制約の削除要求を拒否します。 これがデフォルトの動作です。
trigger_name
有効または無効にする単一のトリガの名前です。
ALL
テーブルに属するすべてのトリガを有効または無効にします。 (外部キー制約、遅延可能な一意性および排他制約を実装するために使用される、内部向けに生成される制約トリガが含まれる場合、スーパーユーザ権限が必要です。)
USER
外部キー制約、遅延可能な一意性および排他制約を実装するために使用される、内部向けに生成されるトリガを除く、テーブルに属するトリガすべてを有効または無効にします。
index_name
既存のインデックスの名前です。
storage_parameter
テーブルの格納パラメータの名前です。
value
テーブルの格納パラメータの新しい値です。 パラメータによりこれは数値となることも文字列となることもあります。
parent_table
このテーブルに関連付ける、または、このテーブルから関連付けを取り除く親テーブルです。
new_owner
テーブルの新しい所有者のユーザ名です。
new_tablespace
テーブルを移動する先のテーブル空間の名前です。
new_schema
テーブルを移動する先のスキーマの名前です。
partition_name
新しいパーティションとして追加する、またはテーブルから切り離すテーブルの名前です。
partition_bound_spec
新しいパーティションのパーティション境界の指定です。 その構文の詳細についてはCREATE TABLEを参照してください。
COLUMN
キーワードには意味がなく、省略可能です。
ADD COLUMN
で列が追加され、非変動性のDEFAULT
が指定されたときには、デフォルトは宣言時に評価されてテーブルのメタデータに格納された結果です。
この値は全ての既存行の列に使われます。
DEFAULT
が指定されなかった場合にはNULLが使われます。
どちらの場合もテーブルを書き直す必要はありません。
変動性のDEFAULT
句を持つ列を追加したり、既存の列の型を変更するには、テーブルとインデックス全体の書き換えが必要になります。
例外として、既存列の型を変更するとき、USING
句が列の内容を変更せず、かつ、古い型が新しい型とバイナリ変換可能であるか新しい型全体に対する制約のないドメインである場合、テーブルの書き換えは必要ありません。
しかし、影響を受ける列に対するインデックスはすべて再構築されなければなりません。
テーブルが巨大な場合、テーブル、インデックスまたはその両方の再構築には非常に時間がかかる可能性があります。
また、一時的に2倍のディスク容量が必要とされます。
CHECK
あるいはNOT NULL
制約を追加する時は、既存の行が制約に従うかどうかを検証するためにテーブルの走査が必要になりますが、テーブルの書き換えは必要ありません。
同様に、新しいパーティションを追加するときは、既存の行がパーティションの制約を満たすかどうかを確認するため、テーブルが走査されるかもしれません。
単一のALTER TABLE
内に複数の変更を指定できるオプションを提供する主な理由は、複数のテーブル走査や書き換えを1回のテーブル走査にまとめることができるようにすることです。
新しい外部キーや検査制約を検証するために大きなテーブルを走査するのは長い時間が掛かる可能性があり、ALTER TABLE ADD CONSTRAINT
コマンドがコミットされるまで、そのテーブルのその他の更新は締め出されます。
NOT VALID
制約オプションの主な目的は、同時実行中の更新に制約を追加する影響を減らすことです。
NOT VALID
を付ければ、ADD CONSTRAINT
コマンドはテーブルを走査せず、すぐにコミットされます。
その後で、VALIDATE CONSTRAINT
コマンドを発行して、既存の行が制約を満たすか検証できます。
他のトランザクションが挿入したり更新したりする行に対しては制約が強制されていることは分かっていますので、この検証操作では同時実行中の更新を締め出す必要はありません。既に存在する行だけ確認する必要があります。
それゆえ、検証には変更するテーブルのSHARE UPDATE EXCLUSIVE
ロックのみが必要です。
(制約が外部キーなら、制約が参照するテーブルのROW SHARE
ロックも必要です。)
同時実行性をさらに向上させるため、テーブルに既に制約違反が存在することを知っている場合にNOT VALID
とVALIDATE CONSTRAINT
を使うことは有用かもしれません。
一度制約が設定されれば、新しい違反は挿入されることはありませんし、既存の問題は、VALIDATE CONSTRAINT
を最終的に成功するまで使って、余裕のある時に修正できます。
DROP COLUMN
構文は、列を物理的には削除せず、SQLの操作に対して不可視にします。
このコマンドを実行した後、テーブルに挿入または更新が行われると、削除した列にはNULLが格納されます。
したがって、列の削除は短時間で行えます。
しかし、削除された列が占めていた領域がまだ回収されていないため、テーブルのディスク上のサイズはすぐには小さくなりません。
この領域は、その後既存の行が更新されるにつれて回収されます。
削除した列が占有していたスペースを即座に再利用できるようにするには、テーブル全体を書き換える構文のALTER TABLE
を使用することができます。
この結果、各行の削除される列がNULL値で再構成されます。
テーブルを書き換える構文のALTER TABLE
はMVCC的に安全ではありません。
同時実行中のトランザクションが、テーブル書き換えが発生する前に取得したスナップショットを使っている場合、テーブルの書き換え後はそのトランザクションにはテーブルが空であるように見えます。
詳しくは13.5を参照して下さい。
SET DATA TYPE
のUSING
オプションでは、その行の古い値を含め、どのような式でも指定できます。
つまり、変換対象の列と同様に、その他の列も参照することができます。
そのため、一般的な変換をSET DATA TYPE
構文で行うことができます。
この柔軟性のため、USING
式は列のデフォルト値には(仮に存在していても)適用されません。
結果が定数式にならない可能性があるためです(デフォルト値は定数式でなければいけません)。
したがって、古い型から新しい型への暗黙キャストや代入キャストが存在しない場合、USING
が指定されていても、SET DATA TYPE
がデフォルト値の変換に失敗する可能性があります。
この場合は、DROP DEFAULT
でデフォルト値を削除し、ALTER TYPE
を実行した後で、SET DEFAULT
を使用して再度適切なデフォルト値を指定してください。
変更対象の列を含むインデックスと制約も同様の配慮が必要です。
そのテーブルを継承するテーブルがある場合、子テーブルに同じ処理を実行しなければ、親テーブルに対する列の追加、列の名前、列の型の変更を実行することはできません。
この制限により、子テーブルの列が常に親テーブルと一致していることが保証されます。
同様に、すべての子テーブルでCHECK
制約の名前を変更し、それが親と子の間で一致するようにしなければ、親テーブルのCHECK
制約の名前を変更することはできません。
(しかし、この制限はインデックスの基づく制約にはあらわれません。)
また、親テーブルからSELECTすると、その子テーブルからもSELECTすることになるため、親テーブルの制約は、それが子テーブルでも有効であると印を付けられるまで、有効であると印を付けられません
これらのすべての場合において、ALTER TABLE ONLY
は受け付けられません。
再帰的なDROP COLUMN
操作では、子テーブルが他の親テーブルからその列を継承しておらず、かつ、その列について独立した定義を持っていない場合のみ、その子テーブルの列を削除します。
再帰的でないDROP COLUMN
(つまり、ALTER TABLE ONLY ... DROP COLUMN
)操作では、継承された列は削除されません。
削除する代わりに、その列は継承されておらず独立して定義されているという印を付けます。
再帰的でないDROP COLUMN
コマンドは、パーティションテーブルでは失敗します。
テーブルのすべてのパーティションは、パーティションの最上位と同じ列を持っていなければならないからです。
IDENTITY列についての操作(ADD GENERATED
、SET
、DROP IDENTITY
など)およびTRIGGER
、CLUSTER
、OWNER
およびTABLESPACE
の操作は子テーブルに再帰的に伝わりません。
つまり、常にONLY
が指定されているかのように動作します。
制約の追加は、NO INHERIT
印がないCHECK
制約に関してのみ再帰的に伝わります。
システムカタログテーブルについては、いかなる部分の変更も許可されていません。
有効なパラメータの詳しい説明はCREATE TABLEを参照してください。 第5章に、継承に関するさらに詳しい情報があります。
varchar
型の列をテーブルに追加します。
ALTER TABLE distributors ADD COLUMN address varchar(30);
これは表の既存の行すべてで、新しい列をNULL値で埋めることになります。
デフォルトが非NULLの列を追加します。
ALTER TABLE measurements ADD COLUMN mtime timestamp with time zone DEFAULT now();
既存の行では、新しい列の値として現在時刻が入ります。また、新しい行では挿入時刻を受け取ります。
列を追加して、後で使われるデフォルトとは異なる値で埋めます。
ALTER TABLE transactions ADD COLUMN status varchar(30) DEFAULT 'old', ALTER COLUMN status SET default 'current';
既存の行はold
で埋められますが、後続のコマンドに対するデフォルトはcurrent
になります。
別々のALTER TABLE
コマンドで2つの副コマンドを発行する場合と、効果は同じです。
テーブルから列を削除します。
ALTER TABLE distributors DROP COLUMN address RESTRICT;
1つの操作で既存の2つの列の型を変更します。
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 RENAME CONSTRAINT zipchk TO zip_check;
列に非NULL制約を付与します。
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
列から非NULL制約を削除します。
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
テーブルとそのすべての子テーブルにCHECK制約を付与します。
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
そのテーブルのみに適用され、その子テーブルには適用されない検査制約を追加します。
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
(この検査制約はこの後作成される子テーブルにも継承されません。)
テーブルとそのすべての子テーブルからCHECK制約を削除します。
ALTER TABLE distributors DROP CONSTRAINT zipchk;
1つのテーブルのみから検査制約を削除します。
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);
自動的に命名される主キー制約をテーブルに付与します。 1つのテーブルが持てる主キーは1つだけであることに注意してください。
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;
範囲パーティションテーブルにパーティションを追加します。
ALTER TABLE measurement ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
リストパーティションテーブルにパーティションを追加します。
ALTER TABLE cities ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
ハッシュパーティションテーブルにパーティションを追加します。
ALTER TABLE orders ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
パーティションテーブルにデフォルトパーティションを追加します。
ALTER TABLE cities ATTACH PARTITION cities_partdef DEFAULT;
パーティションテーブルからパーティションを切り離します。
ALTER TABLE measurement DETACH PARTITION measurement_y2015m12;
(USING INDEX
がない)ADD
、DROP [COLUMN]
、DROP IDENTITY
、RESTART
、SET DEFAULT
、(USING
のない)SET DATA TYPE
、SET GENERATED
、SET
構文は標準SQLに従います。
他の構文は標準SQLに対するPostgreSQLの拡張です。
また、単一のsequence_option
ALTER TABLE
コマンド内に複数の操作を指定する機能もPostgreSQLの拡張です。
ALTER TABLE DROP COLUMN
を使って、1つしか列がないテーブルから列を削除して、列がないテーブルを作成することができます。
これはPostgreSQLの拡張です。SQLでは、列を持たないテーブルは認められていません。