差分

このページの2つのバージョン間の差分を表示します。

--- it技術:データベース:postgresql:oracleからの移行 [2020/01/26 20:53] – [集合演算] yajuadmin
+++ it技術:データベース:postgresql:oracleからの移行 [2023/12/18 13:16] (現在) – [データ型] yajuadmin
@@ 行 6: / 行 6: @@
 ^Oracleのデータ型^PostgreSQL^説明^備考^
-|VARCHAR2(n)|CHARACTER VARYING(n)| 最大n文字の長さの文字列|
+|VARCHAR2(n)|CHARACTER VARYING(n)| 最大n文字の長さの文字列|Oracleはバイト数、PostgreSQLは文字数|
-|NUMBER|NUMERIC|最大1000桁、ユーザ指定精度|
+|NUMBER|NUMERIC|最大1000桁、ユーザ指定精度| |
 |DATE|TIMESTAMP| |DATEのままだと日付のみになる|
 |SYSDATE|current_timestamp  \\ ※SQL92に従いnow()は使わない|現在の日付/時刻を取得する関数|HH24MMSS → HH24MISS|
-|ROWID|oid|32bit(約43億)で一周してしまう|Create文でWITH OIDSと設定しないと使用できない|
+|ROWID|oid|32bit(約43億)で一周してしまう|Create文でWITH OIDSと設定しないと使用できない、PostgreSQL12以降はWITH OIDS廃止|
+| |ctid|ROWIDに相当|バキューム処理によって ctid が変更される可能性があります。|
 参照：[[http://interdbconnect.sourceforge.net/oracle_fdw/data_mapping-ja.html|oracle_fdw データ型マッピング]]
 === 注意点 ===
+OracleはVARCHAR2はバイト数、PostgreSQLはCHARACTER VARYINGは文字数となる。\\
 OracleのNUMBER型をPostgreSQLのNUMERIC型に単純に変更したが、これには弊害もある。\\
 整数しか入らないなら、素直にsmallint型やinteger型にする方がいいかも。
@@ 行 73: / 行 75: @@
 === トランザクションの結果が異なる ===
-複数トランザクションを使用した場合、トランザクションの同時実行の結果が異なる。\\
+複数トランザクションを使用した場合、トランザクションの同時実行の結果が異なる。
-[[https://thinkit.co.jp/article/1063/1|PostgreSQLとOracle Databaseのアーキテクチャ構造]]
-参考：日経SYSTEMS 2019/7
+  * [[https://thinkit.co.jp/article/1063/1|PostgreSQLとOracle Databaseのアーキテクチャ構造]]
+  * [[https://active.nikkeibp.co.jp/atcl/act/19/00050/082900006/|Oracle DBとPostgreSQLで異なる処理結果、「仕様差」問題に要注意]]
 ^時系列^Oracle^^PostgreSQL^^
@@ 行 114: / 行 116: @@
 </code>
+TABLEを作成するよりVIEWにする方が一般的。\\
+[[https://thinkit.co.jp/cert/marugoto/2/1/21/2.htm|PostgreSQLとOracleによるデータベース相互移行マニュアル - ThinkIT]]
+<code sql>
+CREATE VIEW dual AS
+  SELECT 'X' AS DUMMY;
+</code>
@@ 行 173: / 行 182: @@
    WHERE MGR_ID IS NOT NULL;
 </code>
+==== DELETE文のFROM句にJOINが使えない ====
+USING句やIN句に変更する。\\
+[[https://kailo.jp/post/10|PostgreSQLでJOINした結果をDELETEする]]
+==== MERGE文サポート(PostgreSQL 15以降) ====
+PostgreSQL 15でMERGE文がサポートされました。
+  * [[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2022/09/pg15_report_20220906_rev2.pdf|PostgreSQL15 検証レポート - pdf]]
+  * [[https://dev.classmethod.jp/articles/postgresql15-suppoerts-merge-syntax-upsert/|PostgreSQL 15にMERGE文UPSERTがやってくる]]
 ==== 集合演算 ====
-UNION句、INTERSEC句での変更はない。ただし、OracleのMINUS句は、PostgreSQLではEXCEPT句に変更する必要がある。//
+UNION句、INTERSEC句での変更はない。ただし、OracleのMINUS句は、PostgreSQLではEXCEPT句に変更する必要がある。\\
-※単純にMINUS句をEXCEPT句に変更するのではなくインデックスが効くかも見た方がいい。性能検証の段階でEXISTS句に書き換えてインデックスが効くように変更したことがある。
+※性能検証した際にEXCEPT句では遅かったため、NOT EXISTS 書き換えたことがあります。単純に変換すればいいわけではない。
 <code sql oracle>
@@ 行 198: / 行 217: @@
 https://www.postgresql.jp/document/8.0/html/indexes-multicolumn.html
-複合インデックスは最初の列から連続して列が指定されている場合にのみ機能するという制限があるからです。\\
+複合インデックスは**最初の列から連続して列が指定されている場合にのみ機能する**という制限があるからです。\\
 そのため、インデックス定義の途中の列から条件を指定するとインデックスが効かず遅くなる。
 <code>
@@ 行 205: / 行 224: @@
 WHERE AA = xxx AND AB = xxx AND AC = xxx AND AD = xxx
 </code>
+=== bloomインデックスを使用する ===
+主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。\\
+[[https://qiita.com/nuko_yokohama/items/4a7fe2a731a479189034|bloomインデックス]]
+==== ダブルパイプ結合したインデックスは作成できない ====
+PostgreSQLではカラムをダブルパイプ結合するとインデックスが効かなくなります。\\
+Oracleではダブルパイプ結合したインデックス自体を作成できましたが、PostgreSQLではできません。\\
+PostgreSQLでは単一または複合インデックスを作成し、行値式を使用することでインデックスを効くようにします。\\
+<code>
+SELECT * FROM TEST
+WHERE
+(UPDATE_DATE, UPDATE_TIME) >  ('20190218','141435') AND
+(UPDATE_DATE, UPDATE_TIME) <= ('20190219','141435')
+</code>
+※Oracleの行値式は等価(=,!=,IN)しか対応していないが、PostgreSQLでは範囲条件(>, <=など)も対応している。なお、SQLServerは未だに行値式をサポートしていない。
 ==== ユーザ定義関数 ====
 === NVL関数 ===
@@ 行 233: / 行 269: @@
  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;
+</code>
+=== add_month関数 ===
+Oracle関数から移植する際にユーザー関数のADD_MONTHSを作ることでSQL変更を回避する。
+<code 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'
 </code>
@@ 行 329: / 行 392: @@
 Oracleは自律型トランザクションを使用できるが、PostgreSQLでは使用できない。
+=== 対応 ===
+dblinkを使用して自律型トランザクションを実現させる。\\
+[[https://qiita.com/yaju/items/21434db1c08a9fef473a|【PostgreSQL】SELECT内で他のテーブルを更新する]]
 ===== OracleからPostgresSQL移行ツール =====
 ==== 移行支援するツール(ora2pg) ====
@@ 行 394: / 行 461: @@
   * [[https://shobon.hatenablog.com/entry/2014/03/16/142256|テーブル名やカラム名にSQLの予約語を使う方法]]
   * [[https://kaede.jp/2015/06/20210652.html|Oracle⇔Postgresqlの組み込みデータ型対応表]]
+  * [[http://www.meibinlab.jp/nishijima/archives/135|WHERE句でエイリアスを使う]]