OracleからPostgreSQLの移行について下記にまとめる。

• テーブル作成の際に変更が必要なデータ型を下記に記す。

参照：oracle_fdw データ型マッピング

OracleのNUMBER型をPostgreSQLのNUMERIC型に単純に変更したが、これには弊害もある。
整数しか入らないなら、素直にsmallint型やinteger型にする方がいいかも。

弊害としてキャストエラーによりプログラムの修正が発生した。
OracleではNumber型で整数のみだったため(int)でキャストしていたが、PostgreSQLではNUMERIC型だとDecimal型となることによりDataRowを使用した場合、(int)ではキャストエラーになってしまった。よって、Convert.ToInt32でキャストするように修正した。
参照：DataRowから値を取り出すときにdecimalにcastできない

• テーブル作成の際に変更が必要な関数を下記に記す。

Oracleにて、SELECT SUBSTR('123456', -4, 4) FROM DUALを実行すると、｢3456｣が取得できるが、PostgreSQLでは、SELECT SUBSTR('123456',-4, 4)を実行すると、nullが取得できる。

PostgreSQLでは、SELECT RIGHT('123456', 4)で、Oracleと同じ結果が得られる。
第二パラメータが負の場合(右から取得しようとしている)のみ注意が必要。
第二パラメータが正の場合はどちらも同じ結果が得られるので注意しなくてもよい。

第二パラメータの開始インデックスが「0」のとき、Oracleでは開始インデックス「1」と同じだが、PostgreSQLでは前1文字分が削られる。
※第二パラメータの開始インデックスは「1」からと思えばいい。
[データベース比較]部分文字列取得SUBSTRの罠

Oracle : SELECT SUBSTR('ABCDE',0,3) FROM DUAL -> 'ABC'
PostgreSQL : SELECT SUBSTR('ABCDE',0,3) -> 'AB'

SELECT句で Null文字列を含むカラムを連結すると、全体が Null になります。
これを回避するためには、カラムごとに Null の場合の置換を指定することで対処可能です。
[PostgreSQL] PostgreSQL の SELECT句で Null文字列を連結する方法

SELECT ok_column || '-' || COALESCE((null_column, '') FROM foo;

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

PostgreSQLへの移行

仕様差は機能差よりも厄介である。

SQL内で除算を含む計算が実装されている場合、答えが一致しない。これはバグではなく仕様である。
誤差を許容するか、許容できない場合はSQL内で計算せずに計算結果のみをDBに格納するような対応をする。

複数トランザクションを使用した場合、トランザクションの同時実行の結果が異なる。
PostgreSQLとOracle Databaseのアーキテクチャ構造

参考：日経SYSTEMS 2019/7

同じSQLでも結果が変わる。

OracleではDUAL表があるが、PostgreSQLにない。
PostgreSQLはFROMを書く必要がない。

-- Oracle
SELECT '' FROM DUAL
-- PostgreSQL
SELECT ''

移行作業で困らないようにDUAL表を作成することで、Oracleと同じようになる。

CREATE TABLE DUAL (
    DUMMY CHARACTER VARYING(1) DEFAULT '1' NOT NULL
)
WITH (
    OIDS=FALSE
);
 
-- OWNER設定
ALTER TABLE DUAL
    OWNER TO postgres;

引用符が付かない名前は常に小文字に解釈されますが、識別子を引用符で囲むことによって大文字と小文字が区別されるようになります。例えば、識別子FOO、foo、”foo”はPostgreSQLによれば同じものとして解釈されますが、”Foo”と”FOO”は、これら3つとも、またお互いに違ったものとして解釈されます（PostgreSQLが引用符の付かない名前を小文字として解釈することは標準 SQLと互換性がありません。標準SQLでは引用符の付かない名前は大文字に解釈されるべきだとされています。したがって標準SQLによれば、fooは”FOO”と同じであるべきで、”foo”とは異なるはずなのです。もし移植可能なアプリケーションを書きたいならば、特定の名前は常に引用符で囲むか、あるいはまったく囲まないかのいずれかに統一することをお勧めします）。
https://www.postgresql.jp/document/9.6/html/sql-syntax-lexical.html

テーブル名やカラム名を使用した抽出条件があった場合、PostgreSQLでは英小文字になるため英大文字を英子文字に変更する必要がある。

Oracle

 SELECT * FROM 商品マスタ
 WHERE 卸単価 IS NOT NULL
 AND ROWNUM <= 5
 ORDER BY 卸単価 DESC;

PostgreSQL

 SELECT * FROM 商品マスタ
 WHERE 卸単価 IS NOT NULL
 ORDER BY 卸単価 DESC
 LIMIT 5 OFFSET 0;

スカラ副問い合わせの場合は、OracleとPostgreSQLで構文は変わらない。
だだし、FROM句 or WHERE句内で使用する場合、PostgreSQLではエイリアスが必要となる。

• スカラ副問い合わせ

違いなし

 SELECT 商品名 FROM 商品マスタ
 WHERE 卸単価 =
 (SELECT MAX(卸単価) FROM 商品マスタ);

• FROM句内での副問い合わせ

oracle

 SELECT 担当者名
   FROM (SELECT *
            FROM 担当者マスタ
            WHERE 生年月日< '1970-1-1')
   WHERE MGR_ID IS NOT NULL;

PostgreSQL

 SELECT 担当者名
   FROM (SELECT *
            FROM 担当者マスタ
            WHERE 生年月日< '1970-1-1') T
   WHERE MGR_ID IS NOT NULL;

UNION句、INTERSEC句での変更はない。ただし、OracleのMINUS句は、PostgreSQLではEXCEPT句に変更する必要がある。 ※単純にMINUS句をEXCEPT句に変更するのではなくインデックスが効くかも見た方がいい。性能検証の段階でEXISTS句に書き換えてインデックスが効くように変更したことがある。 <code sql oracle> SELECT 担当者ID, 担当者名 FROM 担当者マスタ MINUS SELECT 支店担当者ID, 支店担当者名 FROM 支店担当者マスタ ORDER BY 1; </code> <code sql PostgreSQL> SELECT 担当者ID, 担当者名 FROM 担当者マスタ EXCEPT SELECT 支店担当者ID, 支店担当者名 FROM 支店担当者マスタ ORDER BY 1; </code> ==== 複合インデックスの指定方法 ==== インデックス定義の最初の列から連続する任意数の右方向の列を含む問い合わせに対して複数列インデックスを使用することができます。
https://www.postgresql.jp/document/8.0/html/indexes-multicolumn.html 複合インデックスは最初の列から連続して列が指定されている場合にのみ機能するという制限があるからです。
そのため、インデックス定義の途中の列から条件を指定するとインデックスが効かず遅くなる。 <code> WHERE AC = xxx AND AD = xxx ↓ WHERE AA = xxx AND AB = xxx AND AC = xxx AND AD = xxx </code> ==== ユーザ定義関数 ==== === NVL関数 === Oracle関数から移植する際に、NVL→coalesce(読み:こぅあれす)に変換するは大変なのでユーザー関数のNVLを作ることでSQL変更を回避する。 Porting Oracle NVL to Postgres Coalesce fails <code sql nvl> – 型違いの複数登録が必要 CREATE OR REPLACE FUNCTION nvl(expr1 text, expr2 text) RETURNS text AS $BODY$ SELECT coalesce($1, $2); $BODY$ LANGUAGE sql; CREATE OR REPLACE FUNCTION nvl(expr1 date, expr2 date) RETURNS date AS $BODY$ SELECT coalesce($1, $2); $BODY$ LANGUAGE sql; CREATE OR REPLACE FUNCTION nvl(expr1 numeric, expr2 integer) RETURNS numeric AS $BODY$ SELECT coalesce($1, $2); $BODY$ LANGUAGE sql; </code> === 動的クエリ === 「EXECUTE sql;」として、動的クエリを実行させる。また、「RETURN QUERY」としてテーブルを返す。
PostgreSQL めも - ストアドプロシージャ <code sql> CREATE FUNCTION func_FugaTable(key text) RETURNS TABLE(col1 text, col2 text) AS $$ DECLARE sql TEXT; BEGIN sql := 'SELECT id::text, name::text FROM t_Fuga WHERE id = ' || key || ''; RETURN QUERY EXECUTE sql; END; $$ LANGUAGE plpgsql; </code> ==== トリガ関数 ==== * Oracleではトリガー単体ですんだが、PostgreSQLではトリガーとトリガー専用関数のセットとなっている。 * Oracle は IN、OUT、INOUT というパラメータを関数に渡すことができ、PostgreSQLは IN のみとなっている。
35.11. Oracle PL/SQLからの移植 * PostgreSQLではTRIGGERのREPLACEが存在しないため、更新の際はTRIGGERの削除が必要になる。 トリガ関数の作成と呼び出し方法について記す。 <code sql Oracle> –Insert Beforeトリガ CREATE OR REPLACE TRIGGER TBI_MSTW000010 BEFORE INSERT ON MSTW012010 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN :new.CA := TO_CHAR(SYSDATE,'YYYYMMDD'); :new.CB := TO_CHAR(SYSDATE,'HH24MMSS'); END; –Update Beforeトリガ CREATE OR REPLACE TRIGGER TBU_MSTW000010 BEFORE UPDATE ON MSTW000010 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN :new.CC := TO_CHAR(SYSDATE,'YYYYMMDD'); :new.CD := TO_CHAR(SYSDATE,'HHMMSS'); END; </code> PostgreSQLでは、トリガー専用の関数(引数なし)を別途作成する必要がある。 <code sql PostgreSQL> – トリガ関数 CREATE OR REPLACE FUNCTION db_test.“MSTW000010_insert”() RETURNS trigger AS $BODY$BEGIN NEW.CA := to_char(current_timestamp,'YYYYMMDD'); NEW.CB := to_char(current_timestamp,'HH24MISS'); RETURN NEW; END; BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION db_test.“MSTW000010_insert”() OWNER TO postgres; CREATE OR REPLACE FUNCTION db_test.“MSTW000010_update”() RETURNS trigger AS $BODY$BEGIN NEW.CC := to_char(current_timestamp,'YYYYMMDD'); NEW.CD := to_char(current_timestamp,'HHMISS'); RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION db_test.“MSTW000010_update”() OWNER TO postgres; — Insert Before トリガ CREATE TRIGGER “TBI_MSTW000010” BEFORE INSERT ON db_test.mstw000010 FOR EACH ROW EXECUTE PROCEDURE db_test.“MSTW000010_insert”(); — Update Before トリガ CREATE TRIGGER “TBU_MSTW000010” BEFORE UPDATE ON db_test.mstw000010 FOR EACH ROW EXECUTE PROCEDURE db_test.“MSTW000010_update”(); </code> ==== 自律型トランザクション ==== 自律型トランザクションというのは、実行中のメイン・トランザクションによって開始される独立（自律）したトランザクションのことです。メイン・トランザクションと自律型トランザクションのコミット・ロールバック操作は互いに影響を及ぼさず、完全に独立した別個のトランザクションとなります。 Oracleは自律型トランザクションを使用できるが、PostgreSQLでは使用できない。 ===== OracleからPostgresSQL移行ツール ===== ==== 移行支援するツール(ora2pg) ==== * ora2pg * このora2pgがすごい！Oracleのストアドプロシージャ2種をPostgreSQLに移行してみた！ * how to install ora2pg on windows 7? * OracleからPostgresqlへ移行する * オープンソースミドルウェアへの移行のための 移植開発支援ツールの評価 pdf ora2pgを動作させるには、Perl5が必要 * Windows で使用できるフリーの Perl実行環境 Strawberry Perl ==== Oracle互換関数ライブラリ(oraface) ==== Orafce ホームページへようこそ ==== db_syntaxdiff ==== NTTが開発しているオープンソースのツールである。Ora2Pgが対応していないSELECT文やUPDATE文などのデータ操作を行うSQLに対応している。プログラム言語の中に文字列リテラルとして埋め込まれたSQLコードに対しても利用できる。ただし、実際の修正そのものは人手によって行う必要がある。
https://github.com/db-syntax-diff * APをPostgreSQLへ移植する際の影響箇所を検出するツール * 影響箇所抽出作業の短縮化 * 抽出の網羅性向上、品質の均⼀化 ==== EDB Postgres(Oracle互換性の高いPostgreSQL) ==== EDB PostgresはOracle Databaseとの高い互換性を持っており、Oracle Databaseと同じSQL構文、ファンクション、プロシージャをサポートしています。旧製品名Postgres Plus Advanced Server
EDB Postgres(PostgreSQL) 製品情報 ^Oracleで使用しているSQL^PostgreSQLでの対応^EDB Postgresでの対応^ |・外部結合演算子(+)|・外部結合OUTER JOIN|対応| |・日付、時刻 日付関数や書式|・ほぼすべて変更が必要|一部互換動作に対応| |・条件分岐関数 nvlなど| |対応| |・トランザクション内のROLLBACK挙動|・対応不可|Oracle互換モードを選択可能| |・FROM句内のサブクエリで別名不要|・FROM句内サブクエリの別名必須|対応| |・集合演算子 minus|・集合演算子 except|対応| |・NULLと空文字の区別がない|・対応不可|一部互換動作を設定可能| |・ROWNUM
・MERGE文| |・oidまたはROWNUMで対応（制限あり）
・INSERT ・・・ON CONFRICT| 以下の互換ユーティリティが提供されています。 ^Oracle Database （コマンド）^Postgres Plus （コマンド）^ |SQL*Plus (sqlplus)|EDB*Plus (edbplus.sh)| |SQL*Loader (sqlldr)|EDB*Loader (edbldr)| |Wrap (wrap)|EDB*Wrap (edbwrap)| * PostgresPlus Advanced Server の Oracle Database 互換機能検証 - SlideShare * Vol.3　Postgres Plus Advanced Server（体験編1） * Vol.4　Postgres Plus Advanced Server（体験編2） * 商用DBからPostgreSQLへ　まず知っておいて欲しいまとめ - SlideShare * Oratopostgres-hiroshima - SlideShare ===== 参照 ===== * PostgreSQL select で文字列を連結するときの注意点 * PostgreSQLで文字列の結合(||)をすると勝手にトリムされる(固定長文字列を生成する際には注意) * PostgreSQLで to_char()すると前に半角スペースが入る * PostgreSQLとOracleによるデータベース相互移行マニュアル * Oracle PL/SQL からの移植 * Oracle DatabaseとPostgreSQLの違い * OracleとPostgreSQLの違い PostgreSQLでは、nullと空文字は区別されます。 * [Oracle][PostgreSQL]Date型の違い * Oracle PL/SQL から PostgreSQL PL/pgSQL への移植の注意メモ PostgreSQL では関数をオーバーロードすることができます。 * PostgreSQL の SELECT句で Null文字列を連結する方法 * PostgreSQLのSQL * PostgreSQLでカラムを追加する際にdefault値を設定する方法 * PostgreSQLで自動的にインデックスが生成される条件 * PostgreSQLとMySQLはどちらかに明確な優位性がありますか、というの質問・回答 * テーブル名やカラム名にSQLの予約語を使う方法 * Oracle⇔Postgresqlの組み込みデータ型対応表