oracleからの移行

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

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

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

OracleはVARCHAR2はバイト数、PostgreSQLはCHARACTER VARYINGは文字数となる。
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のアーキテクチャ構造
• Oracle DBとPostgreSQLで異なる処理結果、「仕様差」問題に要注意

同じ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;

TABLEを作成するよりVIEWにする方が一般的。
PostgreSQLとOracleによるデータベース相互移行マニュアル - ThinkIT

CREATE VIEW dual AS
  SELECT 'X' AS DUMMY;

引用符が付かない名前は常に小文字に解釈されますが、識別子を引用符で囲むことによって大文字と小文字が区別されるようになります。例えば、識別子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;

USING句やIN句に変更する。
PostgreSQLでJOINした結果をDELETEする

PostgreSQL 15でMERGE文がサポートされました。

• PostgreSQL15 検証レポート - pdf
• PostgreSQL 15にMERGE文UPSERTがやってくる

UNION句、INTERSEC句での変更はない。ただし、OracleのMINUS句は、PostgreSQLではEXCEPT句に変更する必要がある。
※性能検証した際にEXCEPT句では遅かったため、NOT EXISTS 書き換えたことがあります。単純に変換すればいいわけではない。

oracle

 SELECT 担当者ID, 担当者名
   FROM 担当者マスタ
 MINUS
 SELECT 支店担当者ID, 支店担当者名
   FROM 支店担当者マスタ
 ORDER BY 1;

PostgreSQL

 SELECT 担当者ID, 担当者名
   FROM 担当者マスタ
 EXCEPT
 SELECT 支店担当者ID, 支店担当者名
   FROM 支店担当者マスタ
 ORDER BY 1;

インデックス定義の最初の列から連続する任意数の右方向の列を含む問い合わせに対して複数列インデックスを使用することができます。
https://www.postgresql.jp/document/8.0/html/indexes-multicolumn.html

複合インデックスは最初の列から連続して列が指定されている場合にのみ機能するという制限があるからです。
そのため、インデックス定義の途中の列から条件を指定するとインデックスが効かず遅くなる。

WHERE AC = xxx AND AD = xxx
↓
WHERE AA = xxx AND AB = xxx AND AC = xxx AND AD = xxx

主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。
bloomインデックス

PostgreSQLではカラムをダブルパイプ結合するとインデックスが効かなくなります。
Oracleではダブルパイプ結合したインデックス自体を作成できましたが、PostgreSQLではできません。
PostgreSQLでは単一または複合インデックスを作成し、行値式を使用することでインデックスを効くようにします。

SELECT * FROM TEST
WHERE 
(UPDATE_DATE, UPDATE_TIME) >  ('20190218','141435') AND
(UPDATE_DATE, UPDATE_TIME) <= ('20190219','141435')

※Oracleの行値式は等価(=,!=,IN)しか対応していないが、PostgreSQLでは範囲条件(>, ⇐など)も対応している。なお、SQLServerは未だに行値式をサポートしていない。

Oracle関数から移植する際に、NVL→coalesce(読み:こぅあれす)に変換するは大変なのでユーザー関数のNVLを作ることでSQL変更を回避する。

Porting Oracle NVL to Postgres Coalesce fails

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;
 
CREATE OR REPLACE FUNCTION nvl(expr1 text, expr2 INTEGER)
  RETURNS INTEGER AS
$BODY$
  SELECT COALESCE(CAST($1 AS INT), $2);
$BODY$
  LANGUAGE SQL;
 
CREATE OR REPLACE FUNCTION nvl(expr1 NUMERIC, expr2 text)
  RETURNS text AS
$BODY$
  SELECT COALESCE($1||'', $2);
$BODY$
  LANGUAGE SQL;   

Oracle関数から移植する際にユーザー関数のADD_MONTHSを作ることでSQL変更を回避する。

add_month

CREATE OR REPLACE FUNCTION ADD_MONTHS(TIMESTAMP WITH TIME zone,INTEGER)
  RETURNS TIMESTAMP WITH TIME zone AS
$BODY$
  SELECT $1 + $2 * INTERVAL '1 month'
$BODY$
  LANGUAGE 'sql'

「EXECUTE sql;」として、動的クエリを実行させる。また、「RETURN QUERY」としてテーブルを返す。
PostgreSQL めも - ストアドプロシージャ

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;

• Oracleではトリガー単体ですんだが、PostgreSQLではトリガーとトリガー専用関数のセットとなっている。
• Oracle は IN、OUT、INOUT というパラメータを関数に渡すことができ、PostgreSQLは IN のみとなっている。
  35.11. Oracle PL/SQLからの移植
• PostgreSQLではTRIGGERのREPLACEが存在しないため、更新の際はTRIGGERの削除が必要になる。

トリガ関数の作成と呼び出し方法について記す。

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;

PostgreSQLでは、トリガー専用の関数(引数なし)を別途作成する必要がある。

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"();

自律型トランザクションというのは、実行中のメイン・トランザクションによって開始される独立（自律）したトランザクションのことです。メイン・トランザクションと自律型トランザクションのコミット・ロールバック操作は互いに影響を及ぼさず、完全に独立した別個のトランザクションとなります。

Oracleは自律型トランザクションを使用できるが、PostgreSQLでは使用できない。

dblinkを使用して自律型トランザクションを実現させる。
【PostgreSQL】SELECT内で他のテーブルを更新する

• ora2pg
• このora2pgがすごい！Oracleのストアドプロシージャ2種をPostgreSQLに移行してみた！
• how to install ora2pg on windows 7?
• OracleからPostgresqlへ移行する
• オープンソースミドルウェアへの移行のための 移植開発支援ツールの評価 pdf

ora2pgを動作させるには、Perl5が必要

• Windows で使用できるフリーの Perl実行環境 Strawberry Perl

Orafce ホームページへようこそ

NTTが開発しているオープンソースのツールである。Ora2Pgが対応していないSELECT文やUPDATE文などのデータ操作を行うSQLに対応している。プログラム言語の中に文字列リテラルとして埋め込まれたSQLコードに対しても利用できる。ただし、実際の修正そのものは人手によって行う必要がある。
https://github.com/db-syntax-diff

• APをPostgreSQLへ移植する際の影響箇所を検出するツール
• 影響箇所抽出作業の短縮化
• 抽出の網羅性向上、品質の均⼀化

EDB PostgresはOracle Databaseとの高い互換性を持っており、Oracle Databaseと同じSQL構文、ファンクション、プロシージャをサポートしています。旧製品名Postgres Plus Advanced Server
EDB Postgres(PostgreSQL) 製品情報

以下の互換ユーティリティが提供されています。

• 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の組み込みデータ型対応表
• WHERE句でエイリアスを使う