目次

oracleからの移行

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

変更対象のデータ型、関数

データ型

Oracleのデータ型PostgreSQL説明備考
VARCHAR2(n)CHARACTER VARYING(n) 最大n文字の長さの文字列Oracleはバイト数、PostgreSQLは文字数
NUMBERNUMERIC最大1000桁、ユーザ指定精度
DATETIMESTAMP DATEのままだと日付のみになる
SYSDATEcurrent_timestamp
※SQL92に従いnow()は使わない
現在の日付/時刻を取得する関数HH24MMSS → HH24MISS
ROWIDoid32bit(約43億)で一周してしまうCreate文でWITH OIDSと設定しないと使用できない、PostgreSQL12以降はWITH OIDS廃止
ctidROWIDに相当バキューム処理によって ctid が変更される可能性があります。

参照: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演算子PostgreSQLの演算子説明備考
NVL(文字列,変換文字列) COALESCE(文字列,変換文字列) 文字列がNULLの場合は、変換文字列を返す、NULLでなければ文字列を返す。
INSTR(文字列,検索文字列) POSITION(検索文字列,IN 文字列) 文字列から検索文字列で始まる位置を返す。
DECODE( expr, cmp_expr1, ret_expr1,
cmp_expr2, ret_expr2,
default_expr)
CASE expr
WHEN cmp_expr1 THEN ret_expr1
WHEN cmp_expr2 THEN ret_expr2
ELSE default_expr END
exprがcmp_expr1と同値ならret_expr1を返す、cmp_expr2と同値ならret_expr2を返す、それ以外ならdefault_exprを返す。
SELECT t1.key1, t1.name1, t2.name2
FROM table t1, table t2
WHERE t1.key1 = t2.key2(+)
SELECT t1.key1, t1.name1, t2.name2
FROM table t1 LEFT OUTER JOIN table t2
ON t1.key1 = t2.key
t1の列key1にしか存在しないデータを検索

SUBSTRについて

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;

to_char/to_number は cast に

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

PostgreSQLへの移行

仕様

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

除算に誤差がでる

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

OraclePostgreSQL
1÷3×3=11÷3×3=0.999…
1/3+1/3+1/3=11/3+1/3+1/3=0.999…

トランザクションの結果が異なる

複数トランザクションを使用した場合、トランザクションの同時実行の結果が異なる。

時系列OraclePostgreSQL
トラン1トラン2トラン1トラン2
処理1SQL>select * from test_t;
COL1:COL2
1:1
2:1
3:2
4:2
5:2
5行が選択されました。
SQL>select * from test_t;
COL1:COL2
1:1
2:1
3:2
4:2
5:2
5行が選択されました。
test01=# select * from test_t;
COL1:COL2
1:1
2:1
3:2
4:2
5:2
(5行)
test01=# select * from test_t;
COL1:COL2
1:1
2:1
3:2
4:2
5:2
(5行)
処理2SQL>update test_t set col2=col2+1;
5行が更新されました。
test01=# begin;
BEGIN
update test_t set col2=col2+1; UPDATE 5
処理3 SQL>delete from test_1 where col2=3;
※トラン1が更新のためロック開放待ち
test01=# begin;
BEGIN
delete from test_1 where col2=3;
※処理2の実行が確定していないので処理1の状態をターゲットに更新を保留
処理4SQL>commit;
コミットが完了しました。
test01=#commit;
COMMIT
処理5 2行が削除されました。
SQL>commit;
コミットが完了しました。
※トラン1のロックが開放されて処理3のSQLを処理2の結果に対して実行
DELETE 0
test01=# commi;
COMMIT
※トラン1のロックが開放されて処理3のSQLを実行しようとするが、処理2を実行した結果、処理1の5行目が処理3の実行条件を満たさないので、削除されなかった
処理6SQL>select * from test_t;
COL1:COL2
1:2
2:2
5:4
3行が選択されました。
test01=# select * from test_t;
COL1:COL2
1:2
2:2
3:3
4:3
5:4
(5行)

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

DUAL表の追加

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;

SQL文の移行

テーブル名、カラム名の大文字、小文字の区別

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

結果セットにおける行番号の指定

OraclePostgreSQL
ROWNUM OFFSET
LINE BETWEEN OFFSET + LIMIT
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;

外部結合(アウタージョイン)構文

OraclePostgreSQL
WHERE 表1.列A(+) = 表2.列A FROM 表1 RIGHT OUTER JOIN 表2
ON(表1.列A = 表2.列A)
WHERE 表1.列A = 表2.列A(+) FROM 表1 LEFT OUTER JOIN 表2
ON(表1.列A = 表2.列A)

副問い合わせ

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

違いなし

 SELECT 商品名 FROM 商品マスタ
 WHERE 卸単価 =
 (SELECT MAX(卸単価) 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;

DELETE文のFROM句にJOINが使えない

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

MERGE文サポート(PostgreSQL 15以降)

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

集合演算

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

bloomインデックスを使用する

主キーが複数の場合のインデックスにはデフォルトの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は未だに行値式をサポートしていない。

ユーザ定義関数

NVL関数

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;   

add_month関数

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
 --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内で他のテーブルを更新する

OracleからPostgresSQL移行ツール

移行支援するツール(ora2pg)

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

Oracle互換関数ライブラリ(oraface)

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

db_syntaxdiff

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

EDB Postgres(Oracle互換性の高いPostgreSQL)

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

Oracleで使用しているSQLPostgreSQLでの対応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)

参照