2009-03-26

integer 型カラムを serial 型に変更する

テーマ:DB・SQL

先日、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 の引数にはそういう型にキャストして渡さないといけないということの様です。

よく分かってませんが、そのまま書いてみたら動いたしまあいいか。




AD
いいね!した人  |  コメント(0)  |  リブログ(0)

1700lineさんの読者になろう

ブログの更新情報が受け取れて、アクセスが簡単になります

コメント

[コメントをする]

コメント投稿

AD

ブログをはじめる

たくさんの芸能人・有名人が
書いているAmebaブログを
無料で簡単にはじめることができます。

公式トップブロガーへ応募

多くの方にご紹介したいブログを
執筆する方を「公式トップブロガー」
として認定しております。

芸能人・有名人ブログを開設

Amebaブログでは、芸能人・有名人ブログを
ご希望される著名人の方/事務所様を
随時募集しております。