Postgresqlでadd columnする際の順序制御
困ったこと
現在開発中の株式情報webアプリで、決算情報を持つテーブルにカラムを追加しようとしました。
ですがrails migrationのafterオプションでの順序指定ができず、困っていました。
以下がschema.rbに記載されたテーブルの定義です。
create_table "settlements", force: :cascade do |t| t.bigint "stock_id", null: false t.string "stock_code", null: false t.integer "quarter", null: false t.date "date", null: false t.float "eps", null: false t.float "expected_eps" t.datetime "created_at", null: false t.datetime "updated_at", null: false t.index ["stock_id", "date"], name: "index_settlements_on_stock_id_and_date", unique: true end
上記のテーブルに、年度を示すyaerカラムを追加しようとし、以下のmigrationファイルを作成しました。
class AddColumnToStocks < ActiveRecord::Migration[5.2] def change add_column :stocks, :year, :integer, null: false, after: :stock_code end end
stock_codeカラムの後ろに追加したかったので、afterオプションで順序指定をしています。
上記のmigrationを実行したあとのschema.rbが以下のとおりです。
create_table "settlements", force: :cascade do |t| t.bigint "stock_id", null: false t.string "stock_code", null: false t.integer "quarter", null: false t.date "date", null: false t.float "eps", null: false t.float "expected_eps" t.datetime "created_at", null: false t.datetime "updated_at", null: false t.integer "year", null: false t.index ["stock_id", "date"], name: "index_settlements_on_stock_id_and_date", unique: true end
位置を指定したはずなのに、カラムが末尾に追加されてしまっています。
調べてみたところ、Postgresqlでカラムを追加する場合、末尾にしか追加ができず、位置の指定ができないということでした。
今回は個人開発ということもあり、テーブルを作成するmigrationを作り直して無理やりカラムを追加したのですが、好きな位置にカラムを追加する方法があるとtwitterで教えていただいたので、その内容をまとめてみようと思います。
対策
対策手順を説明すると、以下のとおりです。
- 別のテーブル名で新カラムを加えたテーブルを定義する
- 旧テーブルのレコードを新テーブルにinsertする
- 旧テーブルをdrop
- 新テーブルを旧テーブルと同じ名前にrename
具体的に見ていきましょう。
1. 別のテーブル名で新カラムを加えたテーブルを定義する
文字通り、加えたいカラムを加えた上で、別のテーブル名でテーブルを定義します。
ただ、追加したカラムにnull制約はつけないほうがいいと思います。
手順2でデータをinsertするにあたって、コピー元には追加したカラムの値がないので、制約に引っかかってコピーしたレコードを作成することができません。
必要がある場合は、あとで別途付与しましょう。
create_table "temp_settlements", force: :cascade do |t| t.bigint "stock_id", null: false t.string "stock_code", null: false t.integer "year" t.integer "quarter", null: false t.date "date", null: false t.float "eps", null: false t.float "expected_eps" t.datetime "created_at", null: false t.datetime "updated_at", null: false t.index ["stock_id", "date"], name: "index_settlements_on_stock_id_and_date", unique: true end
2. select * from 旧テーブルを新テーブルにinsertする
以下のようなsqlを書いて、旧テーブルのレコードを新テーブルにinsertします。
insert into temp_settlements (stock_id, stock_code, quarter, date, eps, expected_eps, created_at, updated_at) (select stock_id, stock_code, quarter, date, eps, expected_eps, created_at, updated_at from settlements);
3. 旧テーブルをdrop
以下のようなmigrationファイルを作成して、旧テーブルをdropします。
rails generate migration DropOldTable
class DropOldTable < ActiveRecord::Migration[5.2] def change drop_table :settlements end end
4. 新テーブルを旧テーブルと同じ名前にrename
以下のようなmigrationファイルを作成して、新テーブルを旧テーブルと同じ名前にrenameします。
rails generate migration RenameTempSettlementToSettlement
class RenameTempSettlementToSettlement < ActiveRecord::Migration[5.2] def change rename_table :temp_settlements, :settlements end end
晴れてこれで、任意の位置にカラムを追加したテーブルを作成することができました。
まとめ
- Postgresqlでは、カラムの追加は末尾にしかできない。
- 任意の位置にカラムを追加したい場合は、以下の手順で作業を行う。
- 別のテーブル名で新カラムを加えたテーブルを定義する
- 旧テーブルのレコードを新テーブルにinsertする
- 旧テーブルをdrop
- 新テーブルを旧テーブルと同じ名前にrename