integer 型カラムを serial 型に変更する
先日、integer 型で設計していたカラムを serial 型に変えたくなりました。
変えたいというよりは変えないといけなくなりました、が正しいのかも。
運営期間が長くなるとそういうこともあるもんですね。
数年前の自分たちの設計はなんて古いんだと思いますが、当時は知らなかった手法や使えなかった技術があるんだから仕方がないのだ、と自分に言い聞かせてます。
単に自分がバカだったからとは考えないようにしています。。
integer 型だったカラムを serial 型に変えるには、カラムの型変更の正攻法 では上手くできません。
実際やってみても DB に serial という型は存在しないと言われます。
ALTER TABLE your_table ALTER COLUMN this_col TYPE serial; ERROR: type "serial" does not exist
serial というのは型の種類として扱われていない、ということなんでしょうか。
たしかに psql コマンドラインで見てみると、serial 型は
your_db=> \d your_table
Table "public.your_table"
Column | Type | Modifiers
------------+-----------------------+-----------------------------------
this_col | integer | not null default nextval('your_table_this_col_seq'::regclass)
that_col | text |
という定義がされています。
Type は integer となっていて、 Modifiers との合わせ技で serial というフォーマットを実現している様です。
ということは、運用中の integer 型カラムの Modifiers のところに上記と同じ条件がつけられれば serial 型として振舞えるのではないか、と推測しました。
ここから読み取れる Modifiers の条件は次の3つ。
1. not null
2. default nextval() がセットされている
3. sequence がある
変更前の状況はというと。
DB は PostgreSQL 8.2.3 で、カラムはこんな感じ。
your_db=> \d your_table
Table "public.your_table"
Column | Type | Modifiers
------------+-----------------------+-----------------------------------
this_col | integer | not null
that_col | text |
幸い not null はすでに設定されていたので、 sequence を作ってやって、その sequence に this_col の最大値をセットしてやり、this_col のデフォルト値には出来上がった sequience の nextval を使うようにしてやる、と。
CREATE SEQUENCE your_table_this_col_seq;
SELECT SETVAL('your_table_this_col_seq', (SELECT MAX(this_col) FROM your_table));
ALTER TABLE your_table ALTER this_col SET DEFAULT nextval(your_table_this_col_seq'::regclass);
これで期待通り this_col を省略して INSERT INTO としても自動的に 1 インクリメントされた this_col が設定されるようになりました。
ちなみに ::regclass というのはオブジェクト識別子データ型とかいうものらしく、nextval の引数にはそういう型にキャストして渡さないといけないということの様です。
よく分かってませんが、そのまま書いてみたら動いたしまあいいか。