PostgreSQL 8.2.6文書
前のページ巻戻し早送り次のページ

ALTER TABLE

名前

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

概要

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

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

    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column 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 STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint
    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    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

説明

ALTER TABLEは既存のテーブルの定義を変更します。 このコマンドには、次のような副構文があります。

ADD COLUMN

この構文を使用すると、CREATE TABLEと同じ構文を使って新しい列をテーブルに追加できます。

DROP COLUMN

この構文を使用すると、テーブルから列を削除できます。 削除する列を含んでいるインデックスおよびテーブル制約も自動的に削除されます。 また、削除する列にテーブル以外（例えば、外部キー制約、ビューなど）が依存している場合、CASCADEを付ける必要があります。

ALTER COLUMN TYPE

この構文を使用すると、テーブルの列の型を変更できます。 その列を含むインデックスと簡単なテーブル制約は、元々与えられた式を再解析し、新しい型を使用するように自動的に変換されます。 USING句は、古い列値をどのように新しい値に計算するかを指定します。この句は省略可能です。 省略された場合、デフォルトの変換は、古いデータ型から新しいデータ型への代入キャストと同じになります。 古いデータ型から新しいデータ型への暗黙キャストあるいは代入キャストがない場合、USING句を指定しなければなりません。

SET/DROP DEFAULT

これらの構文を使用すると、列のデフォルト値を設定または削除できます。 指定したデフォルト値は、変更後に行われるINSERTコマンドのみに適用されます。 テーブル内の既存の行は変更されません。 デフォルト値はビューに対して設定することも可能です。 この場合、ビューのON INSERTルールが適用される前に、ビューのINSERT文にデフォルトが挿入されます。

SET/DROP NOT NULL

これらの構文は、列の値としてNULL値を使用できるかどうか設定します。 SET NOT NULLは、その列にNULL値が1つもない場合にのみ設定可能です。

SET STATISTICS

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

SET STORAGE

この構文は、列の保管モードを設定します。 列をインラインで保持するか補足テーブルに保持するか、また、データを圧縮するかどうかを制御できます。 PLAINは、integerのような固定長の値に対して使用します。インラインで保持され、圧縮されません。 MAINは、インラインで保持されていて、圧縮可能なデータに使用します。 EXTERNALは圧縮されていない外部データに使用します。 EXTENDEDは圧縮された外部データに使用します。 EXTENDEDは、PLAIN以外の保管をサポートするほとんどのデータ型におけるデフォルトです。 EXTERNALを使用すると、textおよびbytea列に対する部分文字列操作の処理速度が向上しますが、必要な保管容量が増えるというデメリットがあります。 SET STORAGE自体はテーブルをまったく変更しないことに注意してください。 以後のテーブルの更新時に遂行する戦略を設定するだけです。 詳細は項52.2を参照してください。

ADD table_constraint

この構文を使用すると、CREATE TABLEと同じ構文を使って新しい制約をテーブルに追加できます。

DROP CONSTRAINT

この構文を使用すると、テーブル上の指定した制約を削除できます。

DISABLE/ENABLE TRIGGER

この構文を使用すると、テーブルに属するトリガを無効または有効にすることができます。 無効にされたトリガはシステム上に存在し続けますが、トリガイベントが発生したとしても実行されません。 遅延トリガの場合、有効無効状態の確認は、トリガ関数を実際に実行しようとする時ではなく、イベントの発生時に行われます。 名前でトリガを1つ指定して有効または無効にすることもできますし、テーブル上のすべてのトリガを指定することもできます。 また、ユーザトリガのみを指定することも可能です（このオプションは外部キー制約を実装するために使用されるトリガを除外します）。 制約用のトリガを有効または無効にするにはスーパーユーザ権限が必要です。 トリガが実行されなかった場合に当然ながら制約の整合性が保証されませんので、注意して実行しなければなりません。

CLUSTER

この構文は、以後のCLUSTER操作用のデフォルトインデックスを選択します。 実際のテーブルの再クラスタ化は行いません。

SET WITHOUT CLUSTER

この構文は、テーブルから、一番最後に適用されたCLUSTERインデックス指定を削除します。 以後のインデックスを指定しないクラスタ操作に影響を及ぼします。

SET WITHOUT OIDS

この構文は、テーブルからoidシステム列を削除します。 既にoid列が存在しなくても警告が表示されない点を除けば、DROP COLUMN oid RESTRICTとまったく同等です。

OIDを削除後、再度テーブルにOIDを保存するよう指定するALTER TABLEの副構文は存在しないことに注意してください。

SET ( storage_parameter = value [, ... ] )

この構文は、1つ以上のテーブルの格納パラメータを変更します。 設定可能なパラメータに関してはCREATE TABLEを参照してください。 このコマンドによってテーブルの内容が即座に変更されない点に注意してください。 パラメータによりますが、予定の効果を得るためにテーブルを書き換える必要がある場合があります。 このためには、テーブルを強制的に書き換えるCLUSTERまたはALTER TABLEの構文のいずれかを使用してください。

注意: CREATE TABLEではOIDSをWITH (storage_parameter)構文で指定することができますが、ALTER TABLEではOIDSを格納パラメータとして扱っていません。

RESET ( storage_parameter [, ... ] )

この構文は、1つ以上の格納パラメターをデフォルト値に再設定します。 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以外の全ての操作は、結合して複数の変更リストにまとめて、並行に処理することができます。 例えば、複数の列の追加、型の変更を単一のコマンドで実行することができます。 これは特に巨大なテーブルでは便利です。変更のために必要なテーブル全体の走査が1回で済むからです。

ALTER TABLEコマンドを使用するには、変更するテーブルを所有している必要があります。 テーブルのスキーマを変更するには、新しいスキーマにおけるCREATE権限も持たなければなりません。 親テーブルに新しい子テーブルを追加するには、親テーブルも同様に所有している必要があります。 また、所有者を変更するには、新しい所有ロールの直接あるいは間接的なメンバでなければならず、かつ、そのロールがテーブルのスキーマにおけるCREATE権限を持たなければなりません （この制限により強制的に所有者の変更が、テーブルの削除と再作成を行ってもできないことを行わないようにします。 ただし、スーパーユーザはすべてのテーブルの所有者を変更することができます）。

パラメータ

name

変更対象となる既存のテーブルの名前です（スキーマ修飾名も可）。 ONLYが指定された場合、そのテーブルのみが変更されます。 ONLYが指定されていない場合、そのテーブルおよび（存在する場合は）そのテーブルを継承する全てのテーブルが更新されます。 テーブル名に *を付けると継承テーブルが変更されることを明示できますが、現在のバージョンでは、継承テーブルの変更はデフォルトで行われます （7.1より前のリリースでは、ONLYがデフォルトでした。 デフォルトの設定は、sql_inheritance設定オプションで変更できます）。

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）で初期化されます。

NULLではないデフォルト値を持つ列を追加したり、既存の列の型を変更するには、テーブル全体の書き換えが必要になります。 テーブルが巨大な場合、この処理に非常に時間がかかる可能性があります。また、一時的に2倍のディスク容量が必要とされます。

CHECKあるいはNOT NULL制約を追加する時は、既存の行が制約に従うかどうかを検証するためにテーブルの走査が必要になります。

単一のALTER TABLE内に複数の変更を指定できるオプションを提供する主な理由は、複数のテーブル走査や書き換えを1回のテーブル走査にまとめることができるようにすることです。

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

ALTER TYPEがテーブル全体の書き換えを必要とすることが利点になる場合もあります。 書き換え処理によって、テーブル内の不要となった領域が除去されるからです。 例えば、削除した列が使用していた領域を即座に回収したい場合、最も高速なコマンドは次のようになります。 

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

ここでanycolには既存のテーブル列を、anytypeには既存の列と同一の型を指定します。 このコマンドを実行すると、見た目にはテーブルに有意な変更はありませんが、不要となったデータを取り除く強制的な書き換えが行われます。

ALTER TYPEのUSINGオプションでは、その行の古い値を含め、どのような式でも指定できます。 つまり、変換対象の列と同様に、その他の列も参照することができます。 そのため、一般的な変換をALTER TYPE構文で行うことができます。 この柔軟性のため、USING式は列のデフォルト値には（仮に存在していても）適用されません。 結果が定数式にならない可能性があるためです（デフォルト値は定数式でなければいけません）。 したがって、古い型から新しい型への暗黙キャストや代入キャストが存在しない場合、USINGが指定されていても、ALTER TYPEがデフォルト値の変換に失敗する可能性があります。 この場合は、DROP DEFAULTでデフォルト値を削除し、ALTER TYPEを実行した後で、SET DEFAULTを使用して再度適切なデフォルト値を指定してください。 変更対象の列を持つインデックスと制約も同様の配慮が必要です。

そのテーブルを継承するテーブルがある場合、子テーブルに同じ処理を実行しなければ、親テーブルに対する列の追加、列の名前、型の変更を実行することはできません。 つまり、ALTER TABLE ONLYコマンドは受け付けられません。 この制限により、子テーブルの列が常に親テーブルと一致していることが保証されます。

再帰的なDROP COLUMN操作では、子テーブルが他の親テーブルからその列を継承しておらず、かつ、独立した列定義を持っていない場合のみ、その子テーブルの列を削除します。 再帰的でないDROP COLUMN（例えば、ALTER TABLE ONLY ... DROP COLUMNなど）操作では、継承された列は削除されません。 削除する代わりに、その列は継承されておらず独立して定義されているという印を付けます。

TRIGGER、CLUSTER、OWNERおよびTABLESPACEは子テーブルに再帰的に伝わりません。 つまり、常にONLYが指定されているかのように動作します。 制約の追加は、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 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;

列に非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);

テーブルとその子テーブルからCHECK制約を削除します。 

ALTER TABLE 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);

自動的に命名されるプライマリキー制約をテーブルに付与します。 1つのテーブルが持てるプライマリキーは1つだけであることに注意してください。 

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に従います。 他の構文は標準SQLに対するPostgreSQLの拡張です。 また、単一のALTER TABLEコマンド内に複数の操作を指定する機能もPostgreSQLの拡張です。

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

前のページホーム次のページ
ALTER SEQUENCE上に戻るALTER TABLESPACE