PostgreSQL の古いやつから新しいやつにデータを移行したとき

まあWebのシゴトなんかをしていると、自分が作ったわけでもないサイトのプログラムを新しいサーバーに移転してくれなんつー依頼を受けることがある。

実際問題これいつから放ってあるんだよ？みたいな、プログラム作ったベンダーはともかく、サーバーをレンタルしているサーバー屋さんもこんな古めかしいLinuxディストリビューション提供してて、セキュリティとかどう考えてたんだ？とか言ってもキリがないので言わないけど。

困ったことにそのプログラムを作ったベンダーさんももうなくなっちゃったりしてて、だったら全部ウチに作りなおさせてよといいたいものの、いや言ってはみるものの、そんな予算はないから、新しく借りた新しいディストリビューションのサーバーでなんとか動くようにしてほしいと。

PostgreSQLを使ってるプログラムの場合、まあそのぐらいのベンダーさんが作ったヤツだと、イマドキの新しい、バージョン８以降のPostgreSQLにデータをインポートしてPHPかなんかのプログラムを動かしてみると、こんなエラーが出たりする。

ERROR: operator does not exist: integer = text
HINT: No operator matches the given name and argument type(s).
 You might need to add explicit type casts.

なんじゃこれと思ってよくみると、あるテーブルのプライマリキーが数値型だったとして、それにぶらさがってる別のテーブルの外部キーが文字列型で、無理やりJOINしようとしていたりする。

そんなばかな！と思うかもしれないけれど、そんなケースがとても多いのである。いや、こういうおシゴトは基本的にたくさんはお受けしないのだけど…。まあ型付けとかそんな意識しないような言語ばっかしお使いの、いわいる「ホームページ屋さん」ががんばって作られたシステムなんだろうなあと好意的に思っておくことにする。

で、テーブルを作りなおすのも影響がでかすぎるので、こんなのを実行して数値型と文字型で暗黙の型変換ができるようにしてやったりする。

-- for PostgreSQL 8.2
CREATE FUNCTION int4text(int4) RETURNS text AS 'SELECT textin(int4out($1))' LANGUAGE sql IMMUTABLE STRICT;
CREATE CAST (int4 AS text) WITH FUNCTION int4text(int4) AS IMPLICIT;
CREATE FUNCTION int8text(int8) RETURNS text AS 'SELECT textin(int8out($1))' LANGUAGE sql IMMUTABLE STRICT;
CREATE CAST (int8 AS text) WITH FUNCTION int8text(int8) AS IMPLICIT;

-- for PostgreSQL 8.4 or later
CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;
CREATE CAST (int8 AS text) WITH INOUT AS IMPLICIT;

PostgreSQL 8.2あたりだと上のヤツ、8.4以降だと下のヤツだけでいけると思う。

int4 (つまり integer) と int8 (つまり bigint) を文字列と暗黙の型変換ができるように対応した。必要なひとは int2 (smallint) も定義しておいた方がいいかもしんない。

なんか愚痴っぽい記事になっちゃたなコレ