【PostgreSQL】順番を指定したテーブルの項目追加

PostgreSQL

■ はじめに

https://dk521123.hatenablog.com/entry/2016/06/16/220624

の「使用上の注意」で触れたが、順番を指定した項目追加において
MySQLの場合、AFTER xxxx があるので楽なのだが、
PostgreSQLの場合、項目を追加する際に、
ALTER TABLE ADD COLUMN だと順番指定して追加できない

その対応策として、以下のWikiサイトで2案ある。
そのうちの「1.1 テーブル再作成」をできる限り、間違えずに実践してみた。

https://wiki.postgresql.org/wiki/Alter_column_position/ja

1 列変更の回避方法
　1.1 テーブル再作成 << こっちを選択
　1.2 列を追加しデータを移動

■ 今回の課題

以下の対象テーブルに対して、
"created_at"の手前に、"modified_at" を追加したい

対象テーブル

CREATE TABLE "products" (
    "id" INTEGER NOT NULL,
    "product_name" VARCHAR(50) NOT NULL,
    "price" INTEGER NOT NULL,
    "description" VARCHAR(200) NULL DEFAULT NULL,
    -- ここに "modified_at" を追加したい
    "created_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

■ 手順

【１】対象となるテーブルのCREATE TABLEを用意する
【２】【１】に対して、リネームし、一時的なテーブルを作成する
【３】【１】に対して、SELECT INSERTする
【４】対象テーブルをDROP TABLEする
【５】項目を追加した形の新規テーブルを【１】を元に作成する
【６】【１】に対して、SELECT INSERTする
【７】（必要であれば）新項目にデータを更新する
【８】（問題がなければ）【２】で作成したテーブルを削除する

【１】対象となるテーブルのCREATE TABLEを用意する

チームでCREATE TABLEをちゃんと管理してあればそれを使用。
それがなかったら、さまざまなDBクライアントソフト（e.g. HeidiSQL）などで
用意に取得できる

【２】【１】に対して、リネームし、一時的なテーブルを作成する

【１】の CREATE TABLE をコピペして、
そこから名前だけ変える

サンプル 

/**
 何度もやるなら、DROP TABLE。
 でもはじめは、間違って元ネタのTABLEを削除してしまう可能性があるので
 慣れるまではやめたほうがいい
*/
-- DROP TABLE IF EXISTS "backup_products";
 
CREATE TABLE "backup_products" (
   ... 略 ...

【３】【１】に対して、SELECT INSERTする

以下の構文を参考に、【１】のテーブルに保存する
SELECT INSERT文を書く

構文 

INSERT INTO
  [新しいテーブル]
SELECT
  -- ここは、CREATE TABLE の項目欄からコピペして整形すればいい
  [項目A],
  [項目B],
  ...
FROM
  [古いテーブル]
; 

-- 簡易確認用（どっちも同じ総数なら、ひとまずはOK）
SELECT COUNT(*) FROM [古いテーブル];
SELECT COUNT(*) FROM [新しいテーブル];

【４】対象テーブルをDROP TABLEする

新テーブルを作成する前にシンプルにDROP TABLEする

サンプル 

DROP TABLE products;

【５】項目を追加した形の新規テーブルを【１】を元に作成する

【１】のテーブルをコピペして、対象した項目（今回「modified_at」）を追加する

サンプル 

CREATE TABLE "products" (
    ... 略 ...
    -- ★追加★
    "modified_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    "created_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    ... 略 ...
);

【６】【１】に対して、SELECT INSERTする

【３】で使ったコピペして、テーブル名と新規項目を追加する

【７】（必要であれば）新項目にデータを更新する

新項目に適切な値をUPDATE で更新する

【８】（問題がなければ）【２】で作成したテーブルを削除する

データとして問題がなければ、テーブルを削除する
（リスクを避けるために暫く置いておくともいいと思う）

サンプル 

DROP TABLE backup_products;

■ サンプル

/*
 ちゃんとするならトランザクションを張っておき、
 完了したらコミットするといいが、今回は省略。
*/

-- ■ 一時保存用テーブル（手順【２】）
CREATE TABLE "backup_products" (
    "id" INTEGER NOT NULL,
    "product_name" VARCHAR(50) NOT NULL,
    "price" INTEGER NOT NULL,
    "description" VARCHAR(200) NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

-- ■ 一時保存用テーブルにデータを入れるSQL文（手順【３】）
INSERT INTO
  backup_products
SELECT
  id,
  product_name,
  price,
  description,
  created_at
FROM
  products
; 

-- 簡易確認用（どっちも同じ総数なら、ひとまずはOK）
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM backup_products;

-- ★自信がない場合は、ここで一旦止めたほうがいい★
-- ■ 古いテーブルを削除（手順【４】）
DROP TABLE products;

-- ■ 項目を追加した新テーブルを追加（手順【５】）
CREATE TABLE "products" (
    "id" INTEGER NOT NULL,
    "product_name" VARCHAR(50) NOT NULL,
    "price" INTEGER NOT NULL,
    "description" VARCHAR(200) NULL DEFAULT NULL,
    "modified_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, -- ★追加★
    "created_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

-- ■ 秦テーブルにデータを入れるSQL文（手順【６】）
INSERT INTO
  products
SELECT
  id,
  product_name,
  price,
  description,
  now(), -- 新項目用（今回は現在日時）
  created_at
FROM
  backup_products
; 

-- 簡易確認用（どっちも同じ総数なら、ひとまずはOK）
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM backup_products;

-- 新規項目のデータを更新する（今回は不要のため、省略）

-- 古いテーブルを削除（手順【８】）
DROP TABLE backup_products;