各DB上で表の途中にカラム追加をしてみた｜ITエンジニアとして経験・学習したこと

今回は、Oracle・MySQL・PostgreSQL・SQL Serverの各DB上で、表の途中にカラム追加をしてみたので、共有する。
表の途中にカラム追加をするのは、MySQLであればALTER TABLE文一つで行えるが、それ以外のDBでは、①別テーブルを作成する、②別テーブルに元テーブルのデータを追加する、③元テーブルを削除する、④別テーブルを元テーブル名にリネームする、という手順を踏む必要があることが判明した。

前提条件

Windows端末上に、Oracle・MySQL・PostgreSQL・SQL Serverをそれぞれインストール済であること。また、salesテーブルを作成済であること。

Oracle・MySQL・PostgreSQLのインストール手順は下記記事を参照のこと。

Windows端末にMySQL、PostgreSQL、Oracleをインストールしてみた今回はいろいろな種類のデータベースを使ってみたかったので、Windows端末に、MySQL、PostgreSQL、Oracleそれぞれの...

また、SQL Serverのインストール手順は下記記事を参照のこと。

SQL Serverのデータ分析を行う環境構築を行った(1)今回は、SQL Serverのデータ分析を行う環境構築として、SQL Server・SQL Server Management Stud...

Oracleの場合の実行結果

1) カラム追加前の状態は以下の通り

select * from sales

1	select * from sales

2) カラム追加を行うストアドプログラム(PL/SQL)の実行結果は以下の通り

declare 
   CREATE_NEW_TBL varchar2(512); 
   INSERT_NEW_TBL varchar2(256); 
begin 
   CREATE_NEW_TBL := 'create table new_sales ( ' 
      || ' sale_date date NOT NULL, ' 
      || ' product_name varchar2(50) NOT NULL, ' 
      || ' place_name varchar2(12) NOT NULL, ' 
      || ' place_region varchar2(12), ' 
      || ' sales_amount number(6) NOT NULL) '; 
   INSERT_NEW_TBL := 'insert into new_sales ( ' 
      || ' sale_date, product_name, place_name, place_region, sales_amount ) ' 
      || ' select sale_date, product_name, place_name, null, sales_amount from sales'; 
   execute immediate CREATE_NEW_TBL; 
   execute immediate INSERT_NEW_TBL; 
   execute immediate 'commit'; 
   execute immediate 'drop table sales'; 
   execute immediate 'alter table new_sales rename to sales'; 
end; 
/

declare

CREATE_NEW_TBL varchar2(512);

INSERT_NEW_TBL varchar2(256);

begin

CREATE_NEW_TBL := 'create table new_sales ( '

|| ' sale_date date NOT NULL, '

|| ' product_name varchar2(50) NOT NULL, '

|| ' place_name varchar2(12) NOT NULL, '

|| ' place_region varchar2(12), '

|| ' sales_amount number(6) NOT NULL) ';

INSERT_NEW_TBL := 'insert into new_sales ( '

|| ' sale_date, product_name, place_name, place_region, sales_amount ) '

|| ' select sale_date, product_name, place_name, null, sales_amount from sales';

execute immediate CREATE_NEW_TBL;

execute immediate INSERT_NEW_TBL;

execute immediate 'commit';

execute immediate 'drop table sales';

execute immediate 'alter table new_sales rename to sales';

end;

3) カラム追加後の状態は以下の通り

select * from sales

1	select * from sales

「EaseUS Todo Backup」は様々な形でバックアップ取得が行える便利ツールだったパソコン内のデータを、ファイル/パーティション/ディスク等の様々な単位でバックアップしたり、バックアップのスケジュール設定や暗号化設定も...

MySQLの場合の実行結果

1) カラム追加前の状態は以下の通り

select * from world.sales

1	select * from world.sales

2) カラム追加を行うALTER TABLE文の実行結果は以下の通り

alter table world.sales add place_region varchar(12) after place_name

1	alter table world.sales add place_region varchar(12) after place_name

3) カラム追加後の状態は以下の通り

select * from world.sales

1	select * from world.sales

PostgreSQLの場合の実行結果

1) カラム追加前の状態は以下の通り

select * from sales

1	select * from sales

2) カラム追加を行うストアドプログラム(PL/pgSQL)の実行結果は以下の通り

do $$ 
begin 
   execute 'create table new_sales (' 
      || ' sale_date timestamp NOT NULL, ' 
      || ' product_name varchar(50) NOT NULL, ' 
      || ' place_name varchar(12) NOT NULL, ' 
      || ' place_region varchar(12), ' 
      || ' sales_amount integer NOT NULL )'; 
   execute 'insert into new_sales ( ' 
      || ' sale_date, product_name, place_name, sales_amount ) ' 
      || ' select sale_date, product_name, place_name, sales_amount from sales'; 
   execute 'drop table sales'; 
   execute 'alter table new_sales rename to sales'; 
end$$;

do $$

begin

execute 'create table new_sales ('

|| ' sale_date timestamp NOT NULL, '

|| ' product_name varchar(50) NOT NULL, '

|| ' place_name varchar(12) NOT NULL, '

|| ' place_region varchar(12), '

|| ' sales_amount integer NOT NULL )';

execute 'insert into new_sales ( '

|| ' sale_date, product_name, place_name, sales_amount ) '

|| ' select sale_date, product_name, place_name, sales_amount from sales';

execute 'drop table sales';

execute 'alter table new_sales rename to sales';

end$$;

3) カラム追加後の状態は以下の通り

select * from sales

1	select * from sales

SQL Serverの場合の実行結果

1) カラム追加前の状態は以下の通り

select * from dbo.sales

1	select * from dbo.sales

2) カラム追加を行うストアドプログラム(Transact-SQL)の実行結果は以下の通り

declare @create_new_table varchar(512); 
declare @insert_new_table varchar(256); 
begin 
   set @create_new_table = 'create table dbo.new_sales ( ' 
         + ' sale_date datetime NOT NULL, ' 
         + ' product_name nvarchar(50) NOT NULL, ' 
         + ' place_name nvarchar(12) NOT NULL, ' 
         + ' place_region nvarchar(12), ' 
         + ' sales_amount int NOT NULL );'; 
    set @insert_new_table = 'insert into dbo.new_sales ( ' 
         + ' sale_date, product_name, place_name, place_region, sales_amount ) ' 
         + ' select sale_date, product_name, place_name, null, sales_amount from dbo.sales ;'; 
    execute(@create_new_table); 
    execute(@insert_new_table); 
    execute('drop table dbo.sales'); 
    exec sp_rename 'new_sales', 'sales'; 
end