【JPUG主催】PostgreSQLカンファレンス2020【11月13日開催】
他のバージョンの文書 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

ALTER TABLE

ALTER TABLE — テーブル定義を変更する

概要

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name


ここで、actionは以下のいずれかです。

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
    ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
    ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    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 }
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    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
    DISABLE ROW LEVEL SECURITY
    ENABLE ROW LEVEL SECURITY
    FORCE ROW LEVEL SECURITY
    NO FORCE ROW LEVEL SECURITY
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET TABLESPACE new_tablespace
    SET { LOGGED | UNLOGGED }
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }


また、partition_bound_specは以下のいずれかです。

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )


また、column_constraintは以下の通りです。

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]


また、table_constraintは以下の通りです。

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]


またtable_constraint_using_indexは以下の通りです。

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]


UNIQUEPRIMARY KEY、および、EXCLUDE制約でのindex_parametersは以下の通りです。

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_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

これらの構文を使用すると、列のデフォルト値を設定または削除できます。 デフォルト値は、変更後に行われる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を禁止することができますが、その逆はできません。

ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
SET GENERATED { ALWAYS | BY DEFAULT }
DROP IDENTITY [ IF EXISTS ]

この構文では、列がIDENTITY列であるかどうか、または既存のIDENTITY列の生成属性を変更することができます。 詳細はCREATE TABLEを参照してください。

DROP IDENTITY IF EXISTSが指定され、その列がIDENTITY列でない場合は、エラーを発生させません。 この場合、注意メッセージが発行されます。

SET sequence_option
RESTART

この構文では、既存のIDENTITY列に紐付けられているシーケンスを変更します。 sequence_optionINCREMENT BYなどALTER SEQUENCEがサポートするオプションです。

SET STATISTICS

この構文は、以後のANALYZE操作において、列単位での統計情報収集対象を設定します。 対象として、0から10000までの範囲の値を設定可能です。 また、対象を-1に設定すると、システムのデフォルト統計情報対象(default_statistics_target)が使用されます。 PostgreSQLの問い合わせプランナによる統計情報の使用に関する詳細は、14.2を参照してください。

SET STATISTICSSHARE 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オブションを使うことに関する更なる情報は注釈以下を参照してください。

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として作成された外部キー制約または検査制約を、これらの制約を満たさない行が存在しないことを確認するためにテーブルをスキャンして、検証します。 制約がすでに有効であると記録されている場合は何も起こりません。 (このコマンドの有用性の説明は注釈以下を参照してください。)

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つ以上のテーブルの格納パラメータを変更します。 設定可能なパラメータの詳細に関しては格納パラメータを参照してください。 このコマンドによってテーブルの内容が即座に変更されない点に注意してください。 パラメータによりますが、期待する効果を得るためにテーブルを書き換える必要がある場合があります。 このためには、VACUUM FULLCLUSTERまたはテーブルを強制的に書き換える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付きで作成された)制約は除きます(これらは無視されます)。 一致する子テーブルの制約はすべて継承不可であってはなりません。 現時点ではUNIQUEPRIMARY KEYFOREIGN 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_specCREATE 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

この構文は、指定したパーティションを対象のテーブルから切り離します。 切り離されたパーティションは単独のテーブルとして存在し続けますが、切り離される前のテーブルとの紐付けはなくなります。 対象テーブルのインデックスに付加されていた全てのインデックスも切り離されます。 対象テーブルのものの複製として作られたトリガは削除されます。

RENAMESET SCHEMAATTACH PARTITIONDETACH 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 VALIDVALIDATE CONSTRAINTを使うことは有用かもしれません。 一度制約が設定されれば、新しい違反は挿入されることはありませんし、既存の問題は、VALIDATE CONSTRAINTを最終的に成功するまで使って、余裕のある時に修正できます。

DROP COLUMN構文は、列を物理的には削除せず、SQLの操作に対して不可視にします。 このコマンドを実行した後、テーブルに挿入または更新が行われると、削除した列にはNULLが格納されます。 したがって、列の削除は短時間で行えます。 しかし、削除された列が占めていた領域がまだ回収されていないため、テーブルのディスク上のサイズはすぐには小さくなりません。 この領域は、その後既存の行が更新されるにつれて回収されます。

削除した列が占有していたスペースを即座に再利用できるようにするには、テーブル全体を書き換える構文のALTER TABLEを使用することができます。 この結果、各行の削除される列がNULL値で再構成されます。

テーブルを書き換える構文のALTER TABLEはMVCC的に安全ではありません。 同時実行中のトランザクションが、テーブル書き換えが発生する前に取得したスナップショットを使っている場合、テーブルの書き換え後はそのトランザクションにはテーブルが空であるように見えます。 詳しくは13.5を参照して下さい。

SET DATA TYPEUSINGオプションでは、その行の古い値を含め、どのような式でも指定できます。 つまり、変換対象の列と同様に、その他の列も参照することができます。 そのため、一般的な変換を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 GENERATEDSETDROP IDENTITYなど)およびTRIGGERCLUSTEROWNERおよびTABLESPACEの操作は子テーブルに再帰的に伝わりません。 つまり、常にONLYが指定されているかのように動作します。 制約の追加は、NO INHERIT印がないCHECK制約に関してのみ再帰的に伝わります。

システムカタログテーブルについては、いかなる部分の変更も許可されていません。

有効なパラメータの詳しい説明はCREATE TABLEを参照してください。 第5章に、継承に関するさらに詳しい情報があります。

varchar型の列をテーブルに追加します。

ALTER TABLE distributors ADD COLUMN address varchar(30);

テーブルから列を削除します。

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がない)ADDDROP [COLUMN]DROP IDENTITYRESTARTSET DEFAULT、(USINGのない)SET DATA TYPESET GENERATEDSET sequence_option構文は標準SQLに従います。 他の構文は標準SQLに対するPostgreSQLの拡張です。 また、単一のALTER TABLEコマンド内に複数の操作を指定する機能もPostgreSQLの拡張です。

ALTER TABLE DROP COLUMNを使って、1つしか列がないテーブルから列を削除して、列がないテーブルを作成することができます。 これはPostgreSQLの拡張です。SQLでは、列を持たないテーブルは認められていません。

関連項目

CREATE TABLE