概要
SQLのみで既存のテーブルと(ほぼほぼ)同構造・同データのテーブルを新しく作成する手順になります。
環境
psql --version
psql (PostgreSQL) 9.5.14
ちょっとハマった件
CREATE TABLE ~ AS では完全なテーブル複製はおこなわれません。
CREATE TABLE #{new_table_name} AS SELECT * FROM #{origin_table_name}
上記SQLはフィールドの型やデータを複製したテーブルを簡単に作成できるのですが、型制約やプライマリキー・インデックスなどの定義までは複製することができません。
手順
1. CREATE TABLE + LIKEを使ってテーブル構造をコピー
CREATE TABLE #{new_table_name} (LIKE #{origin_table_name} INCLUDING ALL)
上記SQLを実行するとコピー元テーブルで定義されていたプライマリキーやインデックスキーも新しいテーブルに複製されます。
複製するにあたって論理名は新しいテーブルの名称を元に
プライマリキーは[new_table_name]_pkey
インデックスキーは[new_table_name]_[column_name]_idx
といった具合に自動的に命名されていました。
2. INSERTを使ってデータをコピー
INSERT INTO #{new_table_name} SELECT * FROM #{origin_table_name}
3. シーケンスオブジェクトを作成する
「CREATE TABLE + LIKE」では、シーケンス番号に関する設定はコピー元のテーブル設定がそのまま反映されるため、それぞれのテーブルでデータを登録していくと一方のテーブルに反映される値が飛び番になってしまいます。
よって、必要に応じて新しいテーブル用にシーケンス番号を新規作成し関連付けする必要があります。
まずはシーケンスオブジェクトを作成しましょう。
CREATE SEQUENCE #{new_table_name}_#{colmn_name}_seq start SELECT COUNT(*) FROM #{new_table_name};
4. シーケンス番号をカラムのデフォルト値に関連づける
alter table #{new_table_name} alter #{colmn_name} set default nextval('#{sequence_object_name}');
5. テーブル制御と連動するように変更を加える
alter sequence #{sequence_object_name} owned by #{new_table_name}.#{colmn_name};
owned byを定義しておくと、設定列または対象テーブルを削除ときにそのシーケンスオブジェクトも削除されます
6. 開始値を設定する
select setval('#{sequence_object_name}', [コピーしたデータ数 + 1], false);
7. シーケンス番号の設定を確認してみる
select * from #{sequence_object_name}
以上でコピー元とほぼ同構造で同データの新しいテーブルが作成されます。