ALTER TABLE は既存のテーブルを変更します。よく使うのは ADD COLUMN、RENAME COLUMN ... TO、DROP COLUMN、ALTER COLUMN ... TYPE です。複数の操作をカンマで区切って 1 つの文に書けます。
既存の行は plan が 'free'、signed_up が NULL になります。PostgreSQL 11 からは、定数のデフォルト値付きで列を追加してもテーブルは書き直されないので、大きなテーブルでも速く終わります。clock_timestamp() のような volatile なデフォルト値の場合は、今でもすべての行が書き直されます。
列やテーブルの名前を変更する
名前の変更は一瞬で終わり、データ、インデックス、制約はそのまま残ります。ビューはテーブルの名前ではなく内部 id を参照しているので、引き続き動きます。ただし、ほかの場所にテキストとして書かれた名前は追従しません。PL/pgSQL の関数本体やアプリケーションのクエリは古い名前のままなので、名前を変える前に検索してください。
列を削除する
DROP COLUMN は列とそのデータを削除します。その列にビューや外部キーが依存していると失敗します。CASCADE を付けるとそれらも削除し、IF EXISTS を付けると列がすでにない場合のエラーを避けられます。
このとき PostgreSQL はテーブルを書き直しません。列に削除済みの印を付けるだけで、領域は行が更新されたときか、テーブルが書き直されたときに戻ります。
列の型を変更する
ALTER COLUMN ... TYPE はすべての値を変換します。古い型から新しい型への自動キャストがない場合は、新しい値を計算する式を USING で指定します。
USING がないと、この文は column "price" cannot be cast automatically to type numeric というエラーで失敗します。1 つでも変換できない値があれば文全体が失敗し、テーブルは変更されません。
ほとんどの型変更はテーブルを書き直します。varchar(n) を長くする場合や、varchar を text に変える場合は書き直しません。
デフォルト値と NOT NULL を設定・解除する
SET NOT NULL はテーブルをスキャンし、NULL を持つ行が 1 つでもあれば失敗します。先に空いている値を埋めてください。
DROP DEFAULT と DROP NOT NULL で元に戻せます。新しいデフォルト値はこれからの INSERT にだけ適用され、既存の行は値を保ちます。
制約を追加する
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
CHECK や外部キーを追加すると、検証のためにテーブル全体をスキャンします。大きくてアクセスの多いテーブルでは、まず NOT VALID を付けて追加します。これは新しい行だけをチェックします。そのあと ALTER TABLE orders VALIDATE CONSTRAINT total_positive; を実行します。これは読み書きを止めない弱いロックで既存の行をチェックします。外部キーのページ でも同じ手順を使っています。すべての形式とそのロックは ALTER TABLE のリファレンス に載っています。
よくある質問
PostgreSQL で列の順番は変えられますか?
変えられません。ALTER TABLE は列を移動できず、新しい列は常に最後に追加されます。SELECT で好きな順に列を並べるか、その順番で新しいテーブルを作ってデータをコピーしてください。
PostgreSQL の ALTER TABLE はテーブルをロックしますか?
ほとんどの形式は ACCESS EXCLUSIVE ロックを取り、文が終わるまで読み込みと書き込みをブロックします。名前変更、列の削除、定数のデフォルト値付きの列追加のような速い変更なら、ロックはすぐに解放されます。テーブルを書き直す型変更では、書き直しの間ずっとロックが続きます。
PostgreSQL で複数の列を一度に追加するには?
1 つの文の中で、操作をカンマで区切ります: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;。1 つの ALTER TABLE に含まれる操作は、1 回のロックでまとめて実行されます。
PostgreSQL の ALTER TABLE はロールバックできますか?
できます。PostgreSQL の DDL はトランザクションに対応しているので、BEGIN ... ROLLBACK の中の ALTER TABLE は完全に取り消されます。