データ型の変更時にハマった話 #PostgreSQL

データ型を変更する必要が出た時に、最終的にはできたんだけど、イロイロはまって結局2時間くらいかかってしまったので備忘録。

結論としては、PostgreSQL 9.2.4 では、

制約は外しておく
USING 句を使って明示的にキャスト条件を指定する
該当カラムを View から参照してたら一旦 DROP しとく

ことで、正常にデータ型変換できた。

今回は、今まで 2 値で良かったものが 4 値必要になったので、データ型を boolean から smallint に変更する仕事。

PostgreSQL のオンラインドキュメントには次のように書いてある。

列を異なるデータ型に変換するには以下のようなコマンドを使用してください。

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
これは、その列の既存の項目が新しい型に暗黙的キャストにより変換できる場合にのみ成功します。より複雑な変換が必要な場合、古い値から新しい値をどのように計算するかを指定するUSING句を付けることができます。

なるほどねと思いつつ、とりあえずダメもとで USING 句をつけずに実行してみる。

ALTER TABLE foo ALTER COLUMN bar TYPE smallint;

ERROR: column "bar" cannot be cast automatically to type smallint

うん、案の定怒られました。
で、ちょっと調べて、USING 句をつけて実行してみる。

ALTER TABLE foo ALTER COLUMN bar TYPE smallint
 USING CASE
  WHEN bar = TRUE THEN 1
  WHEN bar = FALSE THEN 0
  ELSE null
 END;

ERROR: default for column "bar" cannot be cast automatically to type smallint

あれ、まだ怒られた。
しばらく考えて気がついたが、NOT NULL 制約と DEFAULT 制約つけてたんだった。
予め外しておく必要があるのか。

ALTER TABLE foo ALTER COLUMN bar DROP NOT NULL;
ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
ALTER TABLE foo ALTER COLUMN bar TYPE smallint
 USING CASE
  WHEN bar = TRUE THEN 1
  WHEN bar = FALSE THEN 0
  ELSE null
 END;

ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view view_baz depends on column "bar"

今度はわかりやすい。
View で参照してるから変更できないよ、と。
えっと、一旦 DROP して後から再作成しろってこと？
それ面倒だなぁ...

DROP VIEW view_baz;
ALTER TABLE foo ALTER COLUMN bar DROP NOT NULL;
ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
ALTER TABLE foo ALTER COLUMN bar TYPE smallint
 USING CASE
  WHEN bar = TRUE THEN 1
  WHEN bar = FALSE THEN 0
  ELSE null
 END;
ALTER TABLE foo ALTER COLUMN bar SET DEFAULT 0;
ALTER TABLE foo ALTER COLUMN bar SET NOT NULL;

CREATE OR REPLACE VIEW view_baz AS ...

やっと正常実行できた！