PostgreSQL から SQL-Server へのテーブルスクリプトおよびクエリ移行のメモ

システムで使っている DB を PostgreSQL から MS SQL-Server へ移行する必要があったため、その時のメモを公開できる形でまとめておきます。動いた結果から書いているため、本来とは違う使い方だったり、使う人の環境やバージョンによっては、この限りでないかもしれません。この情報は参考程度にどうぞ。

■ テーブル生成スクリプト周り

SQL-Server は Transact-SQL に沿わなければならないため、スクリプト系の特殊な構文は全て使えなくなると考えたほうが良い。自動生成している場合、出力結果をコンバートするより、自動生成する箇所で対応する方が手堅いだろう。

他、SQL-Server では、USE で対象 DB を指定し、各定義系の構文においてはスキーマを明記した方が良いと思われる。

□ IF EXISTS

テーブル生成用のスクリプトでは、よく以下の様な DROP TABLE を CREATE TABLE の直前に書いたりすると思うが

DROP TABLE IF EXISTS table_name;

この手は、SQL-Server だと以下のように書き換えが必要である。

IF OBJECT_ID('table_name', 'U') IS NOT NULL
   DROP TABLE table_name;

全く構文が変わってくるので、単純なコンバートを考えると逆に面倒。

□ 主だった型の違い

数値系はほぼ同様。文字列と、日時に関する内容が若干異なる。

varchar → nvarchar 

text → ntext 

timestamptz(3) → datetime

デフォルト値に現在日時を使っている場合

now() → getdate()

□ シーケンス

PostgreSQL の場合、以下で単純に 1 からの連番を生成する bigint が作れる。

CREATE SEQUENCE sequence_name;

続いて連番の取得は以下となる。

SELECT nextval('sequence_name');

SQL-Server で同様のシーケンスを作る場合は以下である。

CREATE SEQUENCE sequence_name AS bigint START WITH 1;

パラメータを使わなかった場合、大半の人にとって意図しない結果となるだろう。
続いて連番の取得は以下となる。

SELECT NEXT VALUE FOR sequence_name;

■ クエリ周り

基本に沿った昔ながらの手堅い SQL を書いている限り、そのままでも大半は動くが、少し癖のある書き方をしていたり、特殊な機能を使っている所は対応が不可欠である。

□ LIMIT

PostgreSQL では抽出件数に制限をかけたい場合 SELECT ... LIMIT num という感じで最後に書くが SQL-Server で単純に同様の事をする場合、先頭に SELECT TOP num ... という形で記述する。

また、アプリ側から PreparedStatement を使って、値を置き換える場合、PostgreSQL の LIMIT で使用する値は置き換え可能だが、SQL-Server の TOP の値は対応していない。（利用するドライバによっては対応しているかもしれない）
※ 単体の SQL が正常に流れることを確認した上で、置き換えが効かない場合は、この点を疑ってみるとよいだろう。

□ 日付のシフト

日付を加算するといった日付の操作をしたい場合は dateadd() 関数を利用すれば良い。

例えば PostgreSQL で以下のような演算の場合

SELECT Cast('2013-12-20' as date) + Cast('+1 Days' AS　INTERVAL)

SQL-Server では次のように書ける

SELECT Dateadd(day, 1, Cast('2013-12-20' as date))

暗黙的な変換を使えばこちらでも問題ない (強制的に datetime 型になるようだ)

SELECT Dateadd(day, 1, '2013-12-20')

□ DISTINCT

PostgreSQL と違い SQL-Server の ntext型は DISTINCT できない。
Count() などの集合関数で Count(DISTINCT t.*) は SQL-Server ではエラー。
Count(DISTINCT t.column) とカラムを明記しなければならない。

□ 外部結合時のレコード存在判定

PostgreSQL だと以下の様なレコードの存在判定が可能であるが、SQL-Server ではエラー。

 SELECT m.*
   FROM main_table m
   LEFT OUTER JOIN sub_table s
     ON s.code = m.code
  WHERE s IS NOT NULL

SQL-Server だと、カラムを省略するとエラーとなるため、
s.code IS NOT NULL のようにテーブルではなくカラムを明記しなければならない。

□ ２項目以上の判定など

PostgreSQL だとサブクエリを使って以下の様な WHERE 句を書いている事が
あるかもしれないが、これは使えない。

WHERE (main, sub) IN (SELECT main, sub ...)

サブクエリを FROM なり JOIN で結合する形で書き換えが必要。
UPDATE でも同様に集合に対して = で更新しようとするとエラーになる。

SQL-Server では単項目の判定を組み合わせて構成しなければならないため、
汎用的な対応を考えるならば、２項目以上を使った記述は一切行わない方が良いだろう。

□ UPDATE や DELETE で USING は使えず、alias の書き方が異なる

以下は、同一テーブル上の仮登録した sub = 1 を対応する本登録の sub = 0 に
置き換えるために一度本登録を削除するといった考えのクエリだが、
これは SQL-Server では使えない。

DELETE FROM table_name t1
 USING table_name t2
 WHERE t2.main = t1.main
   AND t2.sub = 1
   AND t1.sub = 0

SQL-Server だと以下のように別名定義の使い方が違い、単純に式を書けば良い。

DELETE t1
  FROM table_name t1
 INNER JOIN table_name t2
    ON t2.main = t1.main
   AND t2.sub = 1
 WHERE t1.sub = 0

ちなみに、１行目を SELECT * に変えるだけで、削除対象が列挙できる。

最初から両対応を前提に DELETE を書くならば WHERE EXISTS を使った
副問い合わせを使っておく方が良いかもしれない。
この場合はパフォーマンスと汎用性のトレードオフになるか？

□ 文字列結合式

SQL-Server では || は使えない。数値と同様に + で結合する。

■ 最後に

私の場合、上記に挙げた内容で大半の移行が完了できた。当然の話であるが、製品に依存する特徴的な機能を使わず、汎用的に組んでおく事で方が移行が発生した際に楽となるだろう。この点はパフォーマンスをどう考えるかにもよると思われる。