ALTER TABLEでカラム追加・型変更｜MySQL・PostgreSQL比較

ALTER TABLE とは、すでにあるテーブルの定義（カラム・型・制約・名前など）を変更するSQL文です。 カラムを追加するなら ALTER TABLE 表名 ADD COLUMN 列名 型; で、これは MySQL でも PostgreSQL でも同じです。一方、型変更や NOT NULL の付け外しは、両者で構文が大きく異なります。

この記事では、カラム追加・型変更・名前変更・削除・NOT NULL・DEFAULT・制約・インデックス・テーブル名変更の構文を MySQL と PostgreSQL で並べた比較表を先頭に置き、そのあとで「MODIFY で型だけ書いたら NOT NULL とコメントが消えた」「PostgreSQL で USING 句を求められた」といったつまずきを実際に再現します。さらに、100万行のテーブルで各操作の所要時間、MySQL の ALGORITHM=INSTANT／INPLACE／COPY の可否、PostgreSQL のテーブル書き換えの有無とロック（pg_locks）を実行して確かめました。検証は MySQL 8.0.46 と PostgreSQL 16.15 です。

MySQL 8.0 は2026年4月に公式サポートが終了しました。この記事の構文は MySQL 8.4／9.7 でも同じ手順で使えます。版による違い（INSTANT で変更できる回数の上限）は該当箇所で明記します。

ALTER TABLEの早見表（MySQL・PostgreSQL構文比較）

表名を items、すべて実行して確認した構文です。

検証に使うテーブル（コピペ用）

-- MySQL 8.0
CREATE TABLE items (
  id    INT PRIMARY KEY,
  name  VARCHAR(20) NOT NULL DEFAULT '未設定' COMMENT '品名',
  price INT NOT NULL DEFAULT 0 COMMENT '単価',
  code  VARCHAR(10)
) COMMENT='備品';

-- PostgreSQL 16（コメントは別の文で付ける）
CREATE TABLE items (
  id    INT PRIMARY KEY,
  name  VARCHAR(20) NOT NULL DEFAULT '未設定',
  price INT NOT NULL DEFAULT 0,
  code  VARCHAR(10)
);
COMMENT ON COLUMN items.name  IS '品名';
COMMENT ON COLUMN items.price IS '単価';

-- 共通
INSERT INTO items VALUES (1,'ノート',300,'100'),(2,'ペン',120,'200'),(3,'付箋',250,'A-3');

3行目の code にだけ、数字以外を含む 'A-3' を入れてあります。型変更の検証で使います。

カラムを追加する（ADD COLUMN）

ALTER TABLE items ADD COLUMN stock INT NOT NULL DEFAULT 0;

既存の3行には、DEFAULT の値が入ります。MySQL で AFTER name を付けて memo も追加したあとの結果です。

+----+-----------+------+-------+-------+
| id | name      | memo | price | stock |
+----+-----------+------+-------+-------+
|  1 | ノート    | NULL |   300 |     0 |
|  2 | ペン      | NULL |   120 |     0 |
|  3 | 付箋      | NULL |   250 |     0 |
+----+-----------+------+-------+-------+

押さえておきたい点を表にまとめます。

PostgreSQL のエラーは、ALTER TABLE items ADD COLUMN must INT NOT NULL; を実行して確認しました。NOT NULL のカラムを追加するときは、DEFAULT を一緒に書くと両方のDBで同じように動きます。

型を変更する（MODIFY／ALTER COLUMN TYPE）

ここが、両者の構文がもっとも異なるところです。

-- MySQL：カラムの定義を丸ごと書き直す
ALTER TABLE items MODIFY COLUMN price BIGINT NOT NULL DEFAULT 0 COMMENT '単価';

-- PostgreSQL：型だけを変える
ALTER TABLE items ALTER COLUMN price TYPE BIGINT;

PostgreSQL の ALTER COLUMN ... TYPE は型だけを変え、NOT NULL・DEFAULT・コメントはそのまま残ります。 実行後の \d+ items です。

  Column   |         Type          | Nullable |           Default           | Description
-----------+-----------------------+----------+-----------------------------+-------------
 id        | integer               | not null |                             |
 item_name | character varying(20) | not null | '未設定'::character varying | 品名
 price     | bigint                | not null | 0                           | 単価
 code      | character varying(10) |          |                             |
 stock     | integer               | not null | 0                           |
 memo      | character varying(50) |          |                             |

（Collation・Storage などの列は省いています。）

MySQLのMODIFYで型だけ書くと、NOT NULL・DEFAULT・コメントが消える

MySQL の MODIFY COLUMN は「変更」というより「カラム定義の置き換え」です。書かなかった属性は、指定なしとして扱われます。型だけを書いて実行してみます。

-- 変更前： `price` int NOT NULL DEFAULT '0' COMMENT '単価'
ALTER TABLE items MODIFY COLUMN price BIGINT;
SHOW CREATE TABLE items\G

Create Table: CREATE TABLE `items` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL DEFAULT '未設定' COMMENT '品名',
  `memo` varchar(50) DEFAULT NULL,
  `price` bigint DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `stock` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='備品'

price が bigint DEFAULT NULL になり、NOT NULL・DEFAULT 0・コメント「単価」の3つが消えました。 エラーも警告も出ません。この状態で price を省いて INSERT すると、NULL が入ります。

INSERT INTO items (id, name) VALUES (4,'消しゴム');
SELECT id, name, price FROM items WHERE id = 4;

+----+--------------+-------+
| id | name         | price |
+----+--------------+-------+
|  4 | 消しゴム     |  NULL |
+----+--------------+-------+

CHANGE COLUMN でも同じです。名前を変えるつもりで型だけを書くと、属性が消えます。

-- 変更前： `name` varchar(20) NOT NULL DEFAULT '未設定' COMMENT '品名'
ALTER TABLE items CHANGE COLUMN name item_name VARCHAR(20);
-- 変更後： `item_name` varchar(20) DEFAULT NULL

直し方と防ぎ方は次のとおりです。

MODIFY／CHANGE を書く前に SHOW CREATE TABLE で現在の定義をコピーし、変えたい部分だけ書き換えるのが確実な手順です。名前だけ・DEFAULT だけの変更は、定義を書かずに済む RENAME COLUMN・ALTER COLUMN ... SET DEFAULT を使うと、ほかの属性に触れません。RENAME COLUMN 実行後は、属性がすべて残っていました。

  `item_name` varchar(20) NOT NULL DEFAULT '未設定' COMMENT '品名',

PostgreSQLでUSING句が必要になるエラー

文字列型のカラムを整数型に変えようとすると、PostgreSQL は次のエラーを返します。

ALTER TABLE items ALTER COLUMN code TYPE INTEGER;

ERROR:  column "code" cannot be cast automatically to type integer
HINT:  You might need to specify "USING code::integer".

PostgreSQL は、暗黙に変換できない型どうしの変更では、変換のしかたを USING 句で明示するよう求めます。ヒントのとおりに書いてみます。

ALTER TABLE items ALTER COLUMN code TYPE INTEGER USING code::integer;

ERROR:  invalid input syntax for type integer: "A-3"

今度は3行目の 'A-3' が整数に変換できず、エラーになりました。USING 句には任意の式を書けるので、数字以外を取り除いてから変換します。

ALTER TABLE items ALTER COLUMN code TYPE INTEGER
  USING NULLIF(regexp_replace(code, '[^0-9]', '', 'g'), '')::integer;
SELECT id, code FROM items ORDER BY id;

 id | code
----+------
  1 |  100
  2 |  200
  3 |    3

MySQL には USING 句がありません。同じ変更を試すと、変換できない値がある時点でエラーになりました（既定の厳密モード）。

mysql> ALTER TABLE items MODIFY COLUMN code INT;
ERROR 1366 (HY000): Incorrect integer value: 'A-3' for column 'code' at row 3

MySQL で変換のしかたを決めたいときは、「新しいカラムを追加 → UPDATE で変換した値を入れる → もとのカラムを削除 → 名前を変更」の順で進めます。

長さを縮めるとき

データより短い長さに変えると、どちらもエラーで止まり、データは変わりません。

-- MySQL 8.0.46： ALTER TABLE items MODIFY COLUMN item_name VARCHAR(2) NOT NULL;
ERROR 1265 (01000): Data truncated for column 'item_name' at row 1

-- PostgreSQL 16.15： ALTER TABLE items ALTER COLUMN item_name TYPE VARCHAR(2);
ERROR:  value too long for type character varying(2)

カラム名を変更する・カラムを削除する

-- 名前の変更（両DB共通）
ALTER TABLE items RENAME COLUMN name TO item_name;

-- 削除（両DB共通）
ALTER TABLE items DROP COLUMN flag;

-- 複数まとめて削除（両DB共通）
ALTER TABLE items DROP COLUMN flag, DROP COLUMN created_at;

存在しないカラムを削除しようとしたときの表示です。

-- MySQL 8.0.46
ERROR 1091 (42000): Can't DROP 'nothing'; check that column/key exists

-- PostgreSQL 16.15
ERROR:  column "nothing" of relation "items" does not exist
-- DROP COLUMN IF EXISTS nothing にすると NOTICE だけで先に進む
NOTICE:  column "nothing" of relation "items" does not exist, skipping

カラム名を変えると、その名前を使っているアプリケーションのSQL、ビュー、ストアドプロシージャにも影響します。名前変更と削除は、参照している側の修正とセットで進めると安全です。

NOT NULL・DEFAULTを変更する

DEFAULT の構文は共通です。

ALTER TABLE items ALTER COLUMN stock SET DEFAULT 10;
ALTER TABLE items ALTER COLUMN stock DROP DEFAULT;

DEFAULT の変更は、これから INSERT する行にだけ効き、既存の行の値は変わりません。

NOT NULL は構文が分かれます。

-- MySQL：定義を丸ごと書く
ALTER TABLE items MODIFY COLUMN memo VARCHAR(50) NOT NULL;

-- PostgreSQL
ALTER TABLE items ALTER COLUMN memo SET NOT NULL;
ALTER TABLE items ALTER COLUMN memo DROP NOT NULL;

すでに NULL の行があるカラムに NOT NULL を付けると、どちらもエラーになります。

-- MySQL 8.0.46
ERROR 1138 (22004): Invalid use of NULL value

-- PostgreSQL 16.15
ERROR:  column "memo" of relation "items" contains null values

先に UPDATE items SET memo = '' WHERE memo IS NULL; のように NULL を埋めてから実行すると通りました。

制約・インデックスを追加する・削除する

-- 追加（両DB共通）
ALTER TABLE items ADD CONSTRAINT uq_items_code   UNIQUE (code);
ALTER TABLE items ADD CONSTRAINT chk_items_price CHECK (price >= 0);
ALTER TABLE items ADD COLUMN product_id INT,
                  ADD CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id);

既存のデータが制約に合わないと、追加の時点でエラーになります（stock がすべて 0 の状態で CHECK (stock >= 100) を追加）。

-- MySQL 8.0.46
ERROR 3819 (HY000): Check constraint 'chk_items_stock' is violated.

-- PostgreSQL 16.15
ERROR:  check constraint "chk_items_stock" of relation "items" is violated by some row

削除は、MySQL では制約の種類ごとに書き方が変わります。

MySQL で外部キーを削除したあとに SHOW CREATE TABLE を見ると、外部キー用に自動で作られたインデックス KEY `fk_items_product` (`product_id`) が残っていました。不要なら DROP INDEX fk_items_product で別に削除します。PostgreSQL は外部キーを追加しても参照元のカラムにインデックスを作らないので、必要なら CREATE INDEX で自分で作ります。

テーブル名の変更は ALTER TABLE items RENAME TO supplies; が共通で、MySQL には RENAME TABLE items TO supplies; もあります。

トランザクションで取り消せるかの違い

PostgreSQL の ALTER TABLE は、トランザクションの中で実行して ROLLBACK できます。

-- PostgreSQL 16.15
BEGIN;
INSERT INTO items (id, item_name, stock) VALUES (9,'のり',1);
ALTER TABLE items ADD COLUMN tmp INT;
ROLLBACK;
SELECT count(*) FROM items WHERE id = 9;   -- 0（INSERT も取り消された）
-- tmp カラムも存在しない（information_schema.columns で 0 件）

MySQL で同じ流れを実行すると、結果が変わります。

-- MySQL 8.0.46
START TRANSACTION;
INSERT INTO items (id, item_name, stock) VALUES (9,'のり',1);
ALTER TABLE items ADD COLUMN tmp2 INT;
ROLLBACK;
SELECT id, item_name FROM items WHERE id = 9;

+----+-----------+
| id | item_name |
+----+-----------+
|  9 | のり      |
+----+-----------+

MySQL は ALTER TABLE を実行した時点で、それまでの変更を暗黙にコミットします。 そのため ROLLBACK しても INSERT した行は残り、追加したカラムも残ります。MySQL では「元に戻すための ALTER 文」をあらかじめ用意しておくのが手順になります。

MySQLのALGORITHM（INSTANT・INPLACE・COPY）を100万行で確かめる

MySQL（InnoDB）の ALTER TABLE には、3つの実行方式があります。

ALGORITHM=... を省略すると、その操作で使えるもっとも軽い方式が自動で選ばれます。明示して書くと「その方式で実行できないときはエラーで止まる」ので、意図せず時間のかかる方式になるのを防げます。

100万行のテーブルで、方式を指定して実行した結果です。

CREATE TABLE big_items (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL,
                        qty INT NOT NULL, note VARCHAR(50), code VARCHAR(60));
-- 再帰CTEで100万行を投入（4.32秒）

「―」は試していない組み合わせです。時間は mysql クライアントが表示した値で、1回の計測です。

型変更で方式が合わないときのエラー文です。

mysql> ALTER TABLE big_items MODIFY COLUMN qty BIGINT NOT NULL, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

mysql> ALTER TABLE big_items MODIFY COLUMN qty BIGINT NOT NULL, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> ALTER TABLE big_items ADD INDEX idx_qty (qty), ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

結果から読み取れることは3つです。

• カラムの追加・削除・名前変更・DEFAULT 変更は INSTANT で、行数に関係なく一瞬で終わる。
• 型の変更（INT→BIGINT）は COPY だけ。 全行をコピーするので、行数に比例して時間がかかる。
• VARCHAR の拡張は、長さを表すバイト数が変わらない範囲なら INPLACE で一瞬。 utf8mb4 では1文字が最大4バイトなので、VARCHAR(20)→(50)（最大80→200バイト）は通り、(50)→(100)（最大400バイトで255を超える）は COPY になった。

COPYの実行中は更新が待たされる

COPY と INPLACE で、ALTER の実行中に別の接続から1行を UPDATE して、かかった時間を測りました。

COPY の間も読み取りはできますが、更新はコピーが終わるまで待ちます。 行数の多いテーブルで型を変えるときは、更新が少ない時間帯を選ぶ、またはオンラインでスキーマを変更するツール（gh-ost や pt-online-schema-change など。本記事では未実行）を検討するとよいでしょう。

INSTANTには回数の上限がある

INSTANT でカラムを追加・削除するたびに、テーブルの「行バージョン」が1つ増えます。現在の値は次のSQLで確認できます。

SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 't143/big_items';

+----------------+--------------------+
| NAME           | TOTAL_ROW_VERSIONS |
+----------------+--------------------+
| t143/big_items |                  1 |
+----------------+--------------------+

公式ドキュメント（MySQL 8.4 リファレンスマニュアル「Online DDL Operations」）によると、上限は 64 で、MySQL 9.1.0 以降は 255 です。上限に達すると ERROR 4092 (HY000): Maximum row versions reached になります（このエラーは本記事では再現しておらず、公式ドキュメントで確認）。ALTER TABLE big_items ENGINE=InnoDB; でテーブルを作り直すと（100万行で2.23秒）、値が 0 に戻ることを確認しました。

メタデータロックの待ちにも気を配る

INSTANT で一瞬のはずの ALTER が、長く待たされることがあります。別の接続がそのテーブルを使ったトランザクションを開いたままにしていると、ALTER はそれが終わるまで待つためです。待ち時間の上限は lock_wait_timeout で指定できます。

-- 別の接続が START TRANSACTION; SELECT COUNT(*) FROM items; のまま止まっている状態で
SET SESSION lock_wait_timeout = 2;
ALTER TABLE items ADD COLUMN waited INT;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2秒で諦めて戻ってきました。既定値は 31536000 秒（1年）なので、ALTER の前にセッション単位で短く設定しておくと、待ちの連鎖を避けられます。

PostgreSQLの書き換えの有無とロックを確かめる

PostgreSQL の ALTER TABLE は、操作によって「カタログ（定義情報）だけを変える」「全行を検査する」「テーブルを書き換える」の3段階に分かれます。書き換えが起きたかどうかは、pg_relation_filenode('表名')（テーブルの実ファイルの番号）が実行の前後で変わるかで判定できます。

100万行の big_items で測った結果です（\timing on の表示）。

ポイントは次のとおりです。

• 定数や now() の DEFAULT 付きのカラム追加は、行数に関係なく一瞬（PostgreSQL 11 以降の動作）。clock_timestamp() のように行ごとに値が変わる関数を DEFAULT にすると、書き換えになる。
• VARCHAR の長さを広げる・TEXT にするのは一瞬。 縮める、INT を BIGINT にする、は書き換えになる。
• DROP COLUMN は一瞬。カラムを見えなくするだけで、ディスク上の領域はその後の書き換え（VACUUM FULL など）で回収される。

pg_locksでロックの強さを見る

PostgreSQL では ALTER TABLE をトランザクションの中で実行できるので、コミットする前に pg_locks を見れば、どのロックを取ったかが分かります。

BEGIN;
ALTER TABLE items ADD COLUMN l1 INT;
SELECT locktype, mode, granted FROM pg_locks WHERE relation = 'items'::regclass;
ROLLBACK;

 locktype |        mode         | granted
----------+---------------------+---------
 relation | AccessExclusiveLock | t

同じ方法で確かめた結果を並べます。

○・待つ は、PostgreSQL 公式ドキュメント「Explicit Locking」のロック競合表に基づく整理です。カラムの追加・型変更・名前変更・削除・NOT NULL など、ほとんどの ALTER TABLE は AccessExclusiveLock を取り、その間は SELECT も待ちます。 一瞬で終わる操作なら影響は小さく、書き換えを伴う操作では所要時間のあいだ読み書きが止まる、という関係です。ロックの調べ方そのものは「PostgreSQLロック確認SQL」の記事にまとめています。

lock_timeoutで待ちすぎを防ぐ

一瞬で終わる ALTER でも、ロックを「取るまで」の待ちが長引くことがあります。別の接続が items を読んだトランザクションを開いたままにしている状態で試しました。

SET lock_timeout = '2s';
ALTER TABLE items ADD COLUMN waited INT;

ERROR:  canceling statement due to lock timeout

lock_timeout を設定しないと、ALTER は相手が終わるまで待ち続け、待っている ALTER の後ろに、ほかの SELECT も並んで待つことになります。ALTER の前に SET lock_timeout を書き、タイムアウトしたら時間をおいて再実行する、という手順にしておくと影響を小さくできます。

大きなテーブルにNOT NULLを付けるときの手順

SET NOT NULL は全行を検査するあいだ AccessExclusiveLock を持ちます（100万行で102ミリ秒）。PostgreSQL 12 以降は、同じ内容の CHECK 制約が検証済みであれば、この検査を省けます。

ALTER TABLE big_items ADD CONSTRAINT chk_note_nn CHECK (note IS NOT NULL) NOT VALID;  -- 0.3ミリ秒
ALTER TABLE big_items VALIDATE CONSTRAINT chk_note_nn;                                -- 54ミリ秒
ALTER TABLE big_items ALTER COLUMN note SET NOT NULL;                                 -- 0.4ミリ秒
ALTER TABLE big_items DROP CONSTRAINT chk_note_nn;                                    -- 0.3ミリ秒

時間のかかる検査（VALIDATE）は、読み書きを止めない ShareUpdateExclusiveLock で行われます。強いロックを取る文はどれも1ミリ秒未満になりました。NOT VALID は「既存の行は検査せず、これからの行だけ検査する」という指定で、外部キーにも使えます。

100万行での所要時間の比較

同じ内容の操作を、両DBで並べます（MySQL は自動で選ばれる方式、または使えるもっとも軽い方式の値）。

数値は1回の計測で、マシンや行の大きさで変わります。「定義だけを変える操作は一瞬、行を作り直す操作は行数に比例する」という傾向が、どちらのDBにも共通していることが読み取れます。

実行前のチェックリストと判断表

よくある質問

Q. MySQLのMODIFYとCHANGEの違いは何ですか？ A. MODIFY はカラム名を変えずに定義を置き換え、CHANGE は名前と定義を同時に置き換えます。どちらも定義を全部書く必要があり、書かなかった NOT NULL・DEFAULT・コメントは消えます。名前だけを変えるなら、定義を書かずに済む RENAME COLUMN が向いています。

Q. ALTER TABLE は ROLLBACK できますか？ A. PostgreSQL ではトランザクションの中で実行すれば ROLLBACK できます。MySQL では ALTER TABLE の時点で暗黙にコミットされるため、ROLLBACK しても元に戻りません。直前の INSERT なども一緒に確定する点に注意してください。

Q. カラムの順番を変えられますか？ A. MySQL は MODIFY COLUMN 列名 定義 AFTER 別の列名 で変えられます（ALGORITHM=INPLACE を付けて実行できることを確認。公式ドキュメントではテーブルの再構築を伴う操作）。PostgreSQL にはカラムの順番を変える構文がないので、SELECT で並べる列を指定するか、ビューを用意します。

Q. ALGORITHM=INSTANT は MySQL 8.4 や 9.7 でも使えますか？ A. 使えます。8.0.29 以降は末尾以外の位置への追加やカラム削除も INSTANT の対象で、8.4 以降でも同じです。INSTANT で追加・削除できる回数の上限は 64 で、9.1.0 以降は 255 に広がっています（公式ドキュメントで確認）。

まとめ

• カラム追加・削除・名前変更・DEFAULT 変更の構文は、MySQL と PostgreSQL でほぼ共通。型変更と NOT NULL は構文が分かれる。
• MySQL の MODIFY／CHANGE は定義の置き換え。型だけ書くと NOT NULL・DEFAULT・コメントが消えるので、SHOW CREATE TABLE の定義をコピーして書く。
• PostgreSQL で暗黙に変換できない型変更は USING 句が必要。式を書けるので、変換できない値の扱いも決められる。
• MySQL は ALGORITHM=INSTANT を明示すると、一瞬で終わらない操作をエラーで知らせてくれる。型変更は COPY になり、その間は更新が待つ。
• PostgreSQL のほとんどの ALTER TABLE は AccessExclusiveLock。書き換えが起きる操作（INT→BIGINT、長さの縮小など）は行数に比例して時間がかかる。
• ロック待ちの上限（lock_wait_timeout／lock_timeout）を ALTER の前に設定しておくと、待ちの連鎖を避けられる。
• 取り消せるのは PostgreSQL だけ。MySQL は逆向きの ALTER 文を先に用意しておく。

株式会社RJCでは、ALTER TABLEによるテーブル定義の変更手順やロックの調べ方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。