PostgreSQLのALTER TABLE ― 列の追加・型の変更・名前の変更と、終わらないときのロック待ち

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

この記事の出力は PostgreSQL 18.6（Ubuntu 26.04 の標準パッケージ）で採りました。

まず見る（結論）

ALTER TABLE emp ADD COLUMN dept varchar(10);                     -- 列の追加
ALTER TABLE emp ALTER COLUMN code TYPE int USING code::int;      -- 型の変更
ALTER TABLE emp ALTER COLUMN dept SET NOT NULL;                  -- NOT NULL にする
ALTER TABLE emp ALTER COLUMN dept SET DEFAULT 'NONE';            -- 既定値
ALTER TABLE emp RENAME COLUMN dept TO dept_code;                 -- 列名の変更
ALTER TABLE emp DROP COLUMN c2;                                  -- 列の削除

• 列は末尾にしか足せない。AFTER は構文エラー
• 文字列から数値への型変更は USING が要る
• ALTER TABLE は表を読んでいるだけのトランザクションにも待たされる。終わらないときは pg_stat_activity で相手を探す

列を追加する

ALTER TABLE emp ADD COLUMN dept varchar(10);
ALTER TABLE emp ADD COLUMN status int NOT NULL DEFAULT 0;
ALTER TABLE emp ADD COLUMN created timestamptz DEFAULT now();
ALTER TABLE emp ADD COLUMN c1 int, ADD COLUMN c2 int;           -- まとめて足す

行がある表に NOT NULL の列を既定値なしで足すと、既存の行が NULL になるので失敗します。

ALTER TABLE emp ADD COLUMN status int NOT NULL;
ERROR:  column "status" of relation "emp" contains null values

DEFAULT を付けると、既存の行にもその値が入ります。DEFAULT now() の列は、既存の3行とも ALTER を実行した時刻で同じ値になりました。

同じ名前の列があると column "dept" of relation "emp" already exists です。ADD COLUMN IF NOT EXISTS にすると NOTICE だけで通ります。

MySQL の AFTER name は使えません（syntax error at or near "AFTER"）。PostgreSQL では列の並びを後から変えられないので、順番を変えたいときは表を作り直します。

型を変える

ALTER TABLE emp ALTER COLUMN code TYPE int;
ERROR:  column "code" cannot be cast automatically to type integer
HINT:  You might need to specify "USING code::integer".

varchar から int のように暗黙に変換できない型は、USING で変換の式を書きます。

ALTER TABLE emp ALTER COLUMN code TYPE int USING code::int;
ERROR:  invalid input syntax for type integer: "A30"

数値にならない値が1行でもあると、全体が失敗します。どの行かはメッセージに出ないので、先に探します。

SELECT id, code FROM emp WHERE code !~ '^[0-9]+$';

その行を直してから実行すると通りました。int → bigint、int → varchar(10) は USING なしで変更できました。SET DATA TYPE は TYPE と同じ意味です。

varchar(20) を varchar(2) に縮めたときは、データが2文字以内だったのでエラーになりませんでした。

PostgreSQL には MySQL の MODIFY が無く、ALTER TABLE emp MODIFY code int は syntax error at or near "MODIFY" です。

表が書き直される変更

型の変更は表全体を書き直すことがあります。pg_class.relfilenode（表のデータファイルの番号）で前後を比べました。

書き直しの間は AccessExclusiveLock を持ち続けるので、その表への読み書きは終わるまで待たされます。

NOT NULL・既定値・名前

ALTER TABLE emp ALTER COLUMN dept SET NOT NULL;     -- NULL の行があると contains null values
ALTER TABLE emp ALTER COLUMN dept DROP NOT NULL;
ALTER TABLE emp RENAME TO employee;                 -- 表の名前
ALTER TABLE emp DROP COLUMN IF EXISTS c2;

変えた結果は psql の \d emp で確かめます。

  Column   |           Type           | Collation | Nullable |          Default
-----------+--------------------------+-----------+----------+---------------------------
 id        | integer                  |           | not null |
 name      | character varying(50)    |           |          |
 code      | character varying(10)    |           |          |
 dept_code | character varying(10)    |           |          | 'NONE'::character varying
 status    | integer                  |           | not null | 0

ビューが参照している列は変えられない

CREATE VIEW v_emp AS SELECT id, name, code FROM emp;

ALTER TABLE emp ALTER COLUMN name TYPE varchar(100);
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v_emp depends on column "name"

ALTER TABLE emp DROP COLUMN code;
ERROR:  cannot drop column code of table emp because other objects depend on it
DETAIL:  view v_emp depends on column code of table emp
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

長さを広げるだけでも失敗しました。ビューを消して型を変え、ビューを作り直します。作り直す前に \d+ v_emp で定義を控えておきます。CASCADE はビューを消すだけで、作り直しはしません。

列名の変更（RENAME COLUMN）はビューがあっても通り、ビューの定義は full_name AS name のように自動で書き換わりました。

ALTER TABLE が終わらない（ロック待ち）

別のセッションが表を SELECT したトランザクションを閉じずにいると、ALTER TABLE はその終了を待ちます。ALTER TABLE が要るのは最も強い AccessExclusiveLock で、読み取りのロックとも両立しないためです。

SELECT pid, wait_event_type, wait_event, state, left(query, 40) AS query
  FROM pg_stat_activity WHERE datname = 'fsv' AND pid <> pg_backend_pid();
  pid  | wait_event_type | wait_event | state  |                  query
-------+-----------------+------------+--------+------------------------------------------
 27268 | Timeout         | PgSleep    | active | BEGIN; SELECT count(*) FROM emp; SELECT
 27277 | Lock            | relation   | active | ALTER TABLE emp ADD COLUMN c4 int;

SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock';
  pid  | pg_blocking_pids
-------+------------------
 27277 | {27268}

pg_blocking_pids に出た pid（ここでは 27268）が待たせている相手です。止めてよい相手なら SELECT pg_terminate_backend(27268); で切ります。

lock_timeout を付けて実行すると、待ち続けずに指定した時間でエラーになって戻ります。

SET lock_timeout = '3s';
ALTER TABLE emp ADD COLUMN c3 int;
ERROR:  canceling statement due to lock timeout

関連: PostgreSQLでテーブル定義を確認する ／ PostgreSQLの「deadlock detected」 ／ MySQLのALTER TABLE