テーブルの作成後に間違いに気付いたり、あるいはアプリケーションの要件が変わったりした場合には、テーブルをいったん削除して再度作成することができます。 しかし、テーブルにデータを入力済みの場合、あるいはそのテーブルが他のデータベースオブジェクト(例えば外部キー制約)によって参照されている場合、これは良い方法ではありません。 そのため、PostgreSQL では既存のテーブルに変更を加えるための一連のコマンドが用意されています。テーブル内のデータを変更するという概念ではないことに注意してください。 ここでは、テーブルの定義や構造を変更することに焦点を合わせます。
次のことができます。
列の追加
列の削除
制約の追加
制約の削除
デフォルト値の変更
列のデータ型の変更
列名の変更
テーブル名の変更
これらの操作は全てALTER TABLEコマンド(本節の説明範囲を超えますので詳細はこちらを参照してください)を使用して行うことができます。
列を追加するには、次のようなコマンドを使用します。
ALTER TABLE products ADD COLUMN description text;
新しい列にはデフォルト値が初期値として入ります(DEFAULT
句を指定しない場合はNULL値が入ります)。
PostgreSQL 11から、定数のデフォルト値の列を追加するためにテーブルの各行がALTER TABLE
実行時に更新される必要はもうありません。
その代わりに、デフォルト値は次回にその行にアクセスされた場合に返され、テーブルが書き換えられた際に適用されるため、ALTER TABLE
は巨大なテーブルでも非常に高速になります。
しかしながら、もしデフォルト値に揮発性(例えば、clock_timestamp()
)がある場合、各行はALTER TABLE
実行時に計算した値に更新される必要があります。
潜在的に長時間の更新作業を避けるため、特に列を主にデフォルト以外の値でとにかく埋めたい場合、デフォルトのない列を追加しUPDATE
を使用して正しい値を挿入することが望ましいかもしれません。
その上で、後述するように期待するデフォルトを追加してください。
次の構文を使用すると、列の制約も同時に定義することができます。
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
実際にはCREATE TABLE
内の列の記述に使用されている全てのオプションが、ここで使用できます。
ただしデフォルト値は与えられている制約を満足するものでなくてはならないことに注意してください。満足しない場合はADD
が失敗します。一方で、新規の列に正しく値を入れた後で制約を追加することができます(下記参照)。
列を削除するには、次のようなコマンドを使用します。
ALTER TABLE products DROP COLUMN description;
列内にある、どんなデータであれ消去します。
またその列に関連するテーブルの制約も消去されます。
しかし、その列が他のテーブルの外部キー制約として参照されている場合は、PostgreSQLは暗黙のうちに制約を消去したりはしません。
CASCADE
を追加することにより列に依存する全てを消去することを許可できます。
ALTER TABLE products DROP COLUMN description CASCADE;
この背後にある一般的な仕組みに関する説明については5.13を参照してください。
制約を追加するには、テーブル制約の構文が使用されます。
ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
非NULL制約はテーブル制約として記述できないので、追加するには次の構文を使用します。
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
制約は即座に検査されますので、制約を追加する前にテーブル内のデータがこれに従っている必要があります。
制約を削除するには、その制約名を知る必要があります。
自分で名前を付けた場合は簡単です。
しかし、自分で名前を付けていない場合はシステム生成の名前が割り当てられているので、それを調べなくてはなりません。
それにはpsqlの\d
コマンドを使用すると便利です。
他のインタフェースにもテーブルの詳細を調べる方法があるかもしれません。
制約名がわかったら、次のコマンドで制約を削除できます。
tablename
ALTER TABLE products DROP CONSTRAINT some_name;
(自動生成された$2
といった制約名を扱う場合は、有効な識別子となるように二重引用符で括る必要があることを忘れないでください。)
列の削除の場合と同じく、何か他のものが依存している制約を削除する場合にはCASCADE
を付ける必要があります。
例えば、外部キー制約は、参照されている列の一意または主キー制約に依存しています。
上記は、非NULL制約以外の全ての制約型に適用できます。 非NULL制約を削除するには、次のようにします。
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
(非NULL制約には名前がないことを想起してください。)
列に新しいデフォルトを設定するには、以下のようなコマンドを使用します。
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
これはテーブル内の既存の行には何も影響を与えないことに注意してください。これは将来のINSERT
コマンドのために単純にデフォルトを変えるだけです。
デフォルト値を削除するには次のようにします。
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
これは、デフォルトをNULLに設定することと同等です。 そのため、定義されていないデフォルト値を削除してもエラーにはなりません。 なぜなら NULL値が暗黙的にデフォルトとなっているからです。
列を異なるデータ型に変換するには以下のようなコマンドを使用してください。
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
これは、その列の既存の項目が新しい型に暗黙的キャストにより変換できる場合にのみ成功します。
より複雑な変換が必要な場合、古い値から新しい値をどのように計算するかを指定するUSING
句を付けることができます。
PostgreSQLは、(もしあれば)列のデフォルト値を新しい型に、同時に、その列に関連する全ての制約も新しい型に変換しようとします。 しかし、こうした変換は失敗するかもしれませんし、予想を超えた結果になってしまうかもしれません。 型を変更する前にその列に関する制約を全て削除し、後で適切に変更した制約を付け直すことが最善な場合がよくあります。