サービス運用開始時には要件が無く、その時の要件に応じて設計しテーブル等のデータベースオブジェクトを作成する事が殆どだと思います。拡張性が求められる事が想定される環境においては、予めテーブルに将来利用する為の列を用意しておいて、必要に応じて該当の列をRENAMEして利用するなどの方法もあるかと思いますが、それにも限界はあるのでMySQLとPostgreSQLにおいて列を追加する場合の挙動の違いをこちらにまとめておきます。
MySQLにおけるDDL
Sample Table
- create tableでサンプル用のテーブルとデータを作成
POC=> CREATE TABLE members (
POC(> id int NOT NULL,
POC(> name VARCHAR(255) NOT NULL,
POC(> age INT NOT NULL,
POC(> salary NUMERIC NOT NULL,
POC(> regist date NOT NULL,
POC(> PRIMARY KEY (id)
POC(> );
CREATE TABLE
POC=> insert into members(id,name,age,salary,regist) values(1,'Mr.T',20,100,now());
INSERT 0 1
POC=> select * from members;
id | name | age | salary | regist
----+------+-----+--------+------------
1 | Mr.T | 20 | 100 | 2022-03-03
(1 row)
POC=> insert into members(id,name,age,salary,regist) values(2,'Mr.U',21,101,now()),(3,'Mr.V',22,102,now());
INSERT 0 2
POC=> select * from members;
id | name | age | salary | regist
----+------+-----+--------+------------
1 | Mr.T | 20 | 100 | 2022-03-03
2 | Mr.U | 21 | 101 | 2022-03-03
3 | Mr.V | 22 | 102 | 2022-03-03
(3 rows)
POC=> insert into members(id,name,age,regist) values(4,'Mr.W',23,now());
INSERT 0 1
POC=> select * from members;
id | name | age | salary | regist
----+------+-----+--------+------------
1 | Mr.T | 20 | 100 | 2022-03-03
2 | Mr.U | 21 | 101 | 2022-03-03
3 | Mr.V | 22 | 102 | 2022-03-03
4 | Mr.W | 23 | | 2022-03-03
(4 rows)参照：13.1.20 CREATE TABLE ステートメント
セッションで利用するワークテーブルに関しては此方を確認下さい。
テーブルに列を追加
MySQLの場合も、マニュアルを読んでONLINE DDLが可能かどうか確認する事が出来ますが、以下の様にALGORITHM=INPLACEオプションを付ける事でDML(SELECT, INSERT, UPDATE, DELETE)を止めずに処理出来るかどうか確認する事が出来ます。INPLACEがエラーになった場合は、ALGORITHM=COPYを試してみて下さい。但し、COPYの場合は、テーブルをバックグラウンドで作成して入れ替える処理をしているので、DDL(ALTER TABLE)実行中は、他のDMLはSELECTのみを実行する事が可能です。
※ ALGORITHM=INSTANT のサポートは、ADD COLUMN および MySQL 8.0.12 のその他の操作で使用できます。
参照：15.12 InnoDB とオンライン DDL / 15.12.2 オンライン DDL のパフォーマンスと同時実行性
MySQLでは以下の様に指定した列の後に新しい列を追加する事が可能です。
mysql> desc members;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(255) | NO | | NULL | |
| age | int | NO | | NULL | |
| salary | decimal(10,0) | NO | | NULL | |
| regist | date | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> alter table members add column memo varchar (100) after salary,ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc members;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(255) | NO | | NULL | |
| age | int | NO | | NULL | |
| salary | decimal(10,0) | NO | | NULL | |
| memo | varchar(100) | YES | | NULL | |
| regist | date | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)列名をRENAMEする場合
mysql> desc members;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(255) | NO | | NULL | |
| age | int | NO | | NULL | |
| salary | decimal(10,0) | NO | | NULL | |
| memo | varchar(100) | YES | | NULL | |
| regist | date | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table members rename column memo to description;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc members;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(255) | NO | | NULL | |
| age | int | NO | | NULL | |
| salary | decimal(10,0) | NO | | NULL | |
| description | varchar(100) | YES | | NULL | |
| regist | date | NO | | NULL | |
+-------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)PostgreSQLにおけるDDL
Sample Table
- create tableでサンプル用のテーブルとデータを作成
POC=# CREATE TABLE members (
POC(# id int NOT NULL,
POC(# name VARCHAR(255) NOT NULL,
POC(# age INT NOT NULL,
POC(# salary NUMERIC NOT NULL,
POC(# regist date NOT NULL,
POC(# PRIMARY KEY (id)
POC(# );
CREATE TABLE
POC=# insert into members(id,name,age,salary,regist) values(1,'Mr.T',20,100,now());
INSERT 0 1
POC=# insert into members(id,name,age,salary,regist) values(2,'Mr.U',21,101,now()),(3,'Mr.V',22,102,now());
INSERT 0 2
POC=# select * from members;
id | name | age | salary | regist
----+------+-----+--------+------------
1 | Mr.T | 20 | 100 | 2022-03-18
2 | Mr.U | 21 | 101 | 2022-03-18
3 | Mr.V | 22 | 102 | 2022-03-18
(3 rows)
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
regist | date | | not null |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=#テーブルに列を追加
postgresSQLは、指定した位置にカラムを追加する方法がありません。列を希望の並び順に変更する為には、テーブルを別名で作成してデータを流し込んでからRENAMEし、古いテーブルを削除する等の方法が必要な様です。
参照：5.6. テーブルの変更
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
regist | date | | not null |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=# ALTER TABLE members add column memo varchar(100);
ALTER TABLE
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
regist | date | | not null |
memo | character varying(100) | | |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=#PostgreSQLで列名を揃えたい場合
新しい列を加えて、①テーブルを作成⇒②データ流し込み⇒③旧テーブルをRENAME⇒④新テーブルを既存のテーブル名に変更⇒必要に応じてインデックスなどもRNAME。
テーブルが外部キー制約やVIEWなどで参照されていた場合は、そちらも対応が必要なので、運用開始後の対応はあまりお勧めできなそうです。
RENAME構文は、インデックスの名前を変更します。 インデックスがテーブル制約（UNIQUE、PRIMARY KEY、EXCLUDEのいずれか）と関連付けられていた場合、制約名も変更されます。 格納されたデータには影響しません。インデックスの名前の変更にはSHARE UPDATE EXCLUSIVEロックが必要です。
ALTER INDEX
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
regist | date | | not null |
memo | character varying(100) | | |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=# CREATE TABLE members_new (
POC(# id int NOT NULL,
POC(# name VARCHAR(255) NOT NULL,
POC(# age INT NOT NULL,
POC(# salary NUMERIC NOT NULL,
POC(# memo varchar(100) NULL,
POC(# regist date NOT NULL,
POC(# PRIMARY KEY (id)
POC(# );
CREATE TABLE
POC=# insert into members_new(id,name,age,salary,regist) select id,name,age,salary,regist from members;
INSERT 0 3
POC=# alter table members RENAME to members_old;
ALTER TABLE
POC=# alter table members_new RENAME to members;
ALTER TABLE
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
memo | character varying(100) | | |
regist | date | | not null |
Indexes:
"members_new_pkey" PRIMARY KEY, btree (id)
POC=# select * from members;
id | name | age | salary | memo | regist
----+------+-----+--------+------+------------
1 | Mr.T | 20 | 100 | | 2022-03-18
2 | Mr.U | 21 | 101 | | 2022-03-18
3 | Mr.V | 22 | 102 | | 2022-03-18
(3 rows)
POC=#
POC=# alter index members_new_pkey rename to members_pkey;
ALTER INDEX
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
memo | character varying(100) | | |
regist | date | | not null |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=#その他：https://wiki.postgresql.org/wiki/Alter_column_position/ja
列名をRENAMEする場合
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
memo | character varying(100) | | |
regist | date | | not null |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=# alter table members rename column memo to description;
ALTER TABLE
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric | | not null |
description | character varying(100) | | |
regist | date | | not null |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
POC=#参考
ONLINE（SELECT、INSERT、UPDATE、DELETE）を停止せずに実行出来るか？
大きなテーブルにDDLを実行した時に、DDLがどれだけ進んでいるのかを確認？