PostgreSQLへの移行

ここ最近、Oracleベースで構築されているC/SシステムのPostgreSQLへの移行作業をしています。
ストアドプロシージャなどは個別に用意しますが、クライアント側は単一のアプリを利用するため、両データベースを切り替えて利用できる仕組みになっています。
O/Rマッパーなどは使っていないため、アプリからダイレクトでクエリを投げているわけですが、当然そのままではうまく動かない部分が出てくるので、それをテストで拾っていきます。
このフェイズは「テストが9割」と言われるのも納得の地味な作業。
移行作業についてはこちらにまとまった情報がありますが、最終的には全てのクエリをチェックする必要があるので、やはりそれなりの手数はかかるようです。

今回、単純なクエリに関してはどちらのデータベースでも動くように変更したので、主だったところを紹介してみます。

– to_char/to_number は cast に

PostgreSQLでのto_char/to_number関数は第2引数が必須のため、省略しているとエラーになってしまいます。
もちろん第2引数を指定する形に統一してもよいのですが、単純な数値→文字/文字→数値変換の場合、かえって記述が面倒なので、cast関数に置き換えてしまうのが手っ取り早いかと。
「cast(value as varchar(255))」、「cast(value as numeric)」といった感じでほぼ統一できると思います。

– 日付型に注意

まず、PostgreSQLにはsysdate/systimestamp関数がないので共通クエリは書けず、now、current_date、current_timestampなどの関数に置き換えが必要になるのですが、これらは機械的な置換で対応できるのでさほど問題にはなりません。
ちなみにcurrent_dateなどが返すdate型には時刻情報が含まれないので、sysdateを使っている箇所はtimestamp型を返すnowに置き換えたほうが無難。
to_dateも同様にdate型のため時刻情報はカットされるので、時刻も含めて扱っているにもかかわらず無造作にdate系関数を使っている場合はtimestamp型に変えましょう。

– 「文字型 = 数値」の比較はエラー

雑なクエリを書いていると暗黙の変換に頼って文字型カラム/変数と数値を直接比較したりすることがありますが、これエラーになります。
逆に数値カラム/変数を文字列で比較するぶんには大丈夫なようですが、いずれにしてもきちんと型を意識したほうが良いですね。

– サブクエリのエイリアス省略不可

文字通り、サブクエリにエイリアス名を付けていないとエラーになります。

– 文字列の大小比較は文字列結合よりも優先評価される

これは若干レアケースかもしれませんが、where句の条件で文字列同士を大小比較するときに、文字列結合を併用していると問題が生じる場合があります。
例えば「’20140724′ <= '201407' || '25'」などと書いた場合、Oracleでは文字列結合が先に評価されるのでは素直にtrueを返してくれますが、PostgreSQLでは大小比較のほうが先に評価されてしまい、結果としてboolean値ではなく「false25」といった文字列が返ってきます。もちろんエラー。 「'20140724' <= ('201407' || '25')」のようにカッコを付ければ解決はしますが、ちょっと嫌らしい現象です。 ちなみに比較演算子にイコールが含まれない場合は大丈夫なようなので、ちょっとしたバグの類なのかもしれません。 まぁ、そもそも文字列を大小比較とかいうのがイミフなのでやめときませうｗ
その他、「nvlをcoalesceに置換」、「from dualが不要」、「rowidがないのでoidで代替」、「months_between、add_monthsがないのでextractで代替」などといった細かい調整も必要でしたが、全体的にそれほど特殊な処理をしていないので概ねスムーズに移行できました。
あまり比べても仕方ないのですが、エラーメッセージが親切だったり、pgAdminツールが便利だったりして、個人的にはOracleよりもPostgreSQLのほうが使いやすい印象でした。
もちろん一番はSQL Serverですがｗ