Go to Qiita Advent Calendar 2024 Top
LoginSignup
67
59

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

PostgreSQLAdvent Calendar 2019Day 8
@yaju(やじゅ)

OracleからPostgreSQL移行について

Last updated at Posted at 2019-12-08

はじめに

これは、PostgreSQL Advent Calendar 2019の8日目の記事となります。
この記事は自分なりに Oracle から PostgreSQL に移行する上で調べたものです。

サーバーリプレース作業(Windows Server 2008R2 + Oracle 11g → Windows Server 2016 + PostgreSQL 9.6) を昨年から準備してきまして、今年の9月から順次展開中です。
当初は Oracle 12c で検討していたのですが、やはりネックとなったのはライセンス料金(2016年の Oracle Database の大幅なライセンス改訂)でした。全国25箇所ありますのでコストがバカになりません。ということでコスト削減もあって、PostgreSQL に変更となりました。
【B4】OracleからPostgreSQLへのDB移行の実際 - pdf

EDB Postgres(Oracle互換性の高いPostgreSQL) は使わず、20近くある既存アプリケーションの SQL を洗い出し地道に OracleからPostgreSQLの移行をしていきました。

PostgreSQLの概要

読み方はポストグレス・キューエル。Ingresの次版(Post)としてPostgresとなった。QLはQuery Languageの略。

PostgreSQLは、追記型アーキテクチャを採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。不要領域を再利用可能な状態にするには、VACUUMコマンドを使います。

将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」を実装して他のデータベースのようにUNDOログをもつようにしたいとのこと。
データベースとして，オープンソースとして，コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題

【2021/08/03追記】
「zheapの現状」によると、zheapの主力開発者だったAmitがEDBを離れるなどがあり、残念ながら「zheap」は停滞している。

サポート終了期限(EOL＝End Of Life)

バージョン 初期リリース日 サポート終了期限
16 2023年09月14日 2028年11月09日
15 2022年10月13日 2027年11月11日
14 2021年09月30日 2026年11月12日
13 2020年09月24日 2025年11月13日
12 2019年10月03日 2024年11月14日
11 2018年10月18日 2023年11月09日
10 2017年10月05日 2022年11月10日
9.6 2016年09月29日 2021年11月11日

【2021/06/25追記】
PostgreSQL 9.6 はサポート終了期限が迫っているため、某N社の最新サポート対応のPostgreSQL 12 にアップグレードをする予定
【2021/08/02追記】
PostgreSQL 12 にアップグレードする記事を書きました。

名称変更

【2021/08/02追記】
PostgreSQL 10では、いくつかのディレクトリ名や関数名などが変更されました。

  • ログファイル出力先のディレクトリ名がpg_logからlogへ変更
  • WALに関連するディレクトリ／関数／コマンドなどでxlogがwal、locationがlsnに変更
  • コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更
PostgreSQL 9.6以前の名称 PostgreSQL 10以降の名称
pg_log log
pg_xlog pg_wal
pg_clog pg_xact
pg_current_xlog_location pg_current_wal_lsn
pg_xlogdump pg_waldump
pg_receivexlog pg_receivewal

環境

PostgreSQL 9.6 なのは準備開始時に PostgreSQL 10.0 がリリース前だったからです。
現時点で PostgreSQL 12(2019/10/3)、PostgreSQL 13(2020/09/24） がリリースされてます。

接続ドライバーは、.NETのアプリケーションでは「Npgsql」に変更、Classic ASPアプリケーション(32bit)ではpsqlodbc(UNICODE版)に変更しました。

管理ツールの「ODBCデータソースアドミニストレータ(32bit)」にてシステムDNSに「PostgreSQL35W PostgreSQL Unicode」を登録して使用する。
PostgreSQL OLDDBドライバーは、遅い上にPostgreSQLのnumeric型フィールド値がうまく取得できないなど問題があるため、ODBCドライバーを使用している。
PostgreSQLのOLEDB接続は使用しない方がいい

Global.asa
Session("ConnectionString") = "DSN=PostgreSQL35W;Server=localhost;Database=db_test;UID=fuga;PWD=hoge;Port=5432;"

設定

他ホストから接続するための設定変更

postgresql.conf
listen_addresses = '*'		# what IP address(es) to listen on;
port = 5432			# (change requires restart)

※変更後は、PostgreSQLサービス再起動

コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。
他ホストから接続するための設定

接続できるクライアントを設定する

pg_hda.conf に設定追加する。

全てのクライアントPCからの接続を許可

pg_hda.conf
# IPv4 local connections:
host all all 0.0.0.0/0 md5

クライアントPCからの接続に制限を付ける

pg_hda.conf
# IPv4 local connections:
host all all 192.168.64.0/24 md5

※認証方式 trust:任意のロール名でパスワードなしで接続可能、md5:パスワード認証
※変更後は、PostgreSQLサービス再起動は不要

ロケール

lc_collateとlc_ctypeはデフォルトで「C」、lc_messages, lc_monetary, lc_numeric, lc_time を今回の運用としては、全て「C」で統一するようにしました。
※弊害としては、pg_logに出力されるログのメッセージは日本語ではなく英語になる

照合順序

照合順序は見落としがちです、設定しておかないと並び順が違うと言われてしまいます。

照合順序「C」(文字コード順)
{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,Ａ,Ｂ,Ｃ,Ｄ,Ｅ,Ｆ,Ｇ,Ｈ,Ｉ,Ｊ,Ｋ,Ｌ,Ｍ,Ｎ,Ｏ,Ｐ,Ｑ,Ｒ,Ｓ,Ｔ,Ｕ,Ｖ,Ｗ,Ｘ,Ｙ,Ｚ}
 
文字コード順
は（清音）
ば（濁音）
ぱ（半濁音）
ハ（清音）
バ（濁音）
パ（半濁音）
照合順序「Japanese_Japan.932」
{a,A,Ａ,b,B,Ｂ,c,C,Ｃ,d,D,Ｄ,e,E,Ｅ,f,F,Ｆ,g,G,Ｇ,h,H,Ｈ,i,I,Ｉ,j,J,Ｊ,k,K,Ｋ,l,L,Ｌ,m,M,Ｍ,n,N,Ｎ,o,O,Ｏ,p,P,Ｐ,q,Q,Ｑ,r,R,Ｒ,s,S,Ｓ,t,T,Ｔ,u,U,Ｕ,v,V,Ｖ,w,W,Ｗ,x,X,Ｘ,y,Y,Ｙ,z,Z,Ｚ}
 
アイウエオ一つずつ「カタカナ → ひらがな」の順に並びます。
ハ（清音）
は（清音）
バ（濁音）
ば（濁音）
パ（半濁音）
ぱ（半濁音）

ログ出力設定変更

logging_collectorを「off」から「on」にして、pg_logフォルダにログ出力させる。
障害発生に備えて設定すべき3つのログ関連パラメーター

postgresql.conf
logging_collector = on		# Enable capturing of stderr and csvlog
log_line_prefix='[%t] %u %d %p[%l] '	# special values:

※変更後は、PostgreSQLサービス再起動

ログ出力例
[2019-04-25 14:37:08 JST]  7496[1] LOG:  database system was shut down at 2019-04-25 14:37:06 JST
[2019-04-25 14:37:08 JST]  7496[2] LOG:  MultiXact member wraparound protections are now enabled
[2019-04-25 14:37:08 JST]  6592[3] LOG:  database system is ready to accept connections
[2019-04-25 14:37:08 JST]  3240[1] LOG:  autovacuum launcher started

【2020/04/03追記】
エラーが発生した時に、クライアントIPとアプリケーションが分かると原因を追いやすい。
%r：クライアントIPアドレスを出力
%a：アプリケーションを出力 例 psql pgAdmin III ほとんどは不明の[unknown]

postgresql.conf
log_line_prefix='[%t] %u %d %p[%l] %r %a '	# special values:
ログ出力例
[2020-03-30 17:42:57 JST] hoge test 5912[1] 127.0.0.1(65080) psql ERROR:  duplicate key value violates unique constraint "pk_sno"
[2020-03-31 10:26:21 JST] hoge test 4172[1] 10.20.30.1(54228) pgAdmin III- ?????????????????? ERROR:  syntax error at or near "elect" at character 1
[2020-03-31 16:21:20 JST] hoge test 7424[1] 192.168.0.105(49160) [unknown] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

クライアントIPを出すようにしたことで接続を強制終了したIPアドレスが遠隔ではなくローカル(127.0.0.1)と分かり、原因を特定することが出来ました。その時は、サーバーの監視ソフトが300秒のタイムアウトとなり接続を強制終了していたのです。

psqlのパスワード入力省略

パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。
%APPDATA%\postgresql\pgpass.conf

pgpass.conf
localhost:5432:*:postgres:(パスワード)

もう1つのやり方として、セキュリティ上は非推奨であるがバッチ内に環境変数(PGPASSWORD)を設定した上で実行する。

SET PGPASSWORD=(パスワード)
psql -U (ユーザー) -f xxxxx.sql

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

データ型

テーブル作成の際に変更が必要なデータ型を下記に記す。※あくまで個人の見解

Oracleのデータ型 PostgreSQL 説明 備考
VARCHAR2(n) CHARACTER VARYING(n) 最大n文字の長さの文字列 Oracleはバイト数だがPostgreSQLは文字数となる
NUMBER NUMERIC 最大1000桁、ユーザ指定精度
DATE TIMESTAMP DATEのままだと日付のみになる
SYSDATE current_timestamp または clock_timestamp
※SQL92に従いnow()は使わない		 現在の日付/時刻を取得する関数 HH24MMSS → HH24MISS
ROWID oid 32bit(約43億)で一周してしまう Create文でWITH OIDSと設定しないと使用できない。PostgreSQL12以降はWITH OIDS廃止

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

【2024/06/13追記】
PostgreSQL の current_date 関数と current_timestamp 関数は、トランザクションの開始日時を常に返す仕様のため、 SYSDATE 関数の代替として実時間を取得したい場合には clock_timestamp 関数が一番近い
SYSDATE 関数を Oracle から PostgreSQL に変換する

注意点

OracleはVARCHAR2はバイト数だがPostgreSQLのCHARACTER VARYINGは文字数となる。
OracleのNUMBER型をPostgreSQLのNUMERIC型に単純に変更したが、これには弊害もある。
整数しか入らないなら、素直にsmallint型やinteger型にする方がいいかも。

弊害として.NETアプリケーションにてキャストエラーによりプログラムの修正が発生した。
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について

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

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

第2引数が負の場合(右から取得しようとしている)は注意が必要。右から取得したい場合は、RIGHT関数を使用する。

-- Oracle
SELECT SUBSTR('123456', -4, 4) FROM DUAL -> '3456'
-- PostgreSQL
SELECT SUBSTR('123456',-4, 4) -> null
SELECT RIGHT('123456', 4) -> '3456'

文字列結合

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

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

仕様

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

PostgreSQL エンタープライズ・コンソーシアム 技術部会 WG#2
データ移行・文字コード変換編 異種 DBMS から PostgreSQL への移行
https://www.pgecons.org/wp-content/uploads/PGECons/2013/WG2/05_DataMigrationResearch_ConvertCharacterCode.pdf

NULLと空文字の扱い

Oracle では NULL と空文字は同一に扱いますが、PostgreSQL では別物として区別します。

除算に誤差がでる

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

Oracle PostgreSQL
1÷3×3=1 1÷3×3=0.999…
1/3+1/3+1/3=1 1/3+1/3+1/3=0.999…

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

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

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では英小文字になるため英大文字を英小文字に変更する必要がある。

文字型 = 数値 の比較はエラー

PostgreSQLの場合、Numeric型にシングルクォーテーションを付けて指定するとエラーになる。
修正例 KBN=1 → KBN='1'

PostgreSQLの場合、1桁のvarchar型にシングルクォーテーションを付けないで指定するとエラーになる。
修正例 KBN='1' → KBN=1

書式

PostgreSQLでは to_char/to_number 関数は第2引数が必須のため、省略しているとエラーになる。
PostgreSQLへの移行

PostgreSQLでは to_char すると先頭に半角スペースが1つ入ってしまう。
to_char の第二引数にFM接頭辞を追加することで勝手に入る半角スペースを無効化出来る。

-- Oracle
SELECT TO_CHAR(10, '00000') FROM DUAL -> '00010'
-- PostgreSQL 先頭の半角スペースが入る
SELECT to_char(10, '00000') -> ' 00010'
-- PostgreSQL
SELECT to_char(10, 'FM00000') -> '00010'

PostgreSQLの数値文字列変換

DUAL表の追加

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

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

【2021/09/08追記】
TABLEを作成するよりVIEWにする方が一般的でしたね。
PostgreSQLとOracleによるデータベース相互移行マニュアル - ThinkIT

CREATE VIEW dual AS
  SELECT 'X' AS DUMMY;

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

Oracle PostgreSQL
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;

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

Oracle PostgreSQL
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 商品マスタ);

FROM句内での副問い合わせ

PostgreSQLでは、サブクエリにエイリアス名(例 T)を付けていないとエラーになります。

-- 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句に変更する。

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;

複合インデックスの指定方法

性能検証をしていて遅かった原因の一つでした。

インデックス定義の最初の列から連続する任意数の右方向の列を含む問い合わせに対して複数列インデックスを使用することができます。
11.3. 複数列インデックス

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

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

Oracleとの違い

Oracleの場合、Oracle 9iから索引内の先頭列が抽出条件に含まれていなくても先頭列をスキップすることで複合索引が使用可能になるINDEX (SKIP SCAN)(索引スキップ・スキャン)機能が追加されましたが、PostgreSQLではまだこの機能がないため、インデックスが使われる為には先頭のキーが必ず必要になる。

あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。C1, C2, C3 の順で順序付けされています。
複合インデックスは、構成列のどの列を指定すれば選択候補に挙がるのか。

C1 C2 C3 Oracle
複合インデックス利用可不可		 PostgreSQL
複合インデックス利用可不可
○ ○ ○ 可 可
○ ○ × 可 可
○ × × 可 可
○ × ○ 可 ※C1のみ、C3は無視 可 ※C1のみ、C3は無視
× ○ ○ 可 ※索引スキップ・スキャン 可 ※table scanが選択される可能性高い
× ○ × 可 ※索引スキップ・スキャン 可 ※table scanが選択される可能性高い
× × ○ 可 ※索引スキップ・スキャン 可 ※table scanが選択される可能性高い
× × × 不可 不可

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

主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、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;

日付計算、時間計算

PostgreSQLでは、ADD_MONTHS関数など日付計算、時間計算が用意されていないので、必要なのはユーザー関数で作成しておくといい。

interval '1 month'など、interval型を使う。
PostgreSQLで日付計算、時間計算の書き方

add_months
CREATE FUNCTION add_months(in timestamptz, in int4)
    RETURNS timestamptz 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内で他のテーブルを更新する

その他

  • PostgreSQLの場合、テーブルのカラム名に予約語(例 “DO”)があるとそのままでは使えない。二重引用符"do"を付与すれば使える
  • PostgreSQLの場合、SELECTで別名にしたカラム名をORDER BY句で指定できないため、一段上にSELECTを追加する必要がある
  • PostgreSQLの場合、MAX値取得でORDER BY句があるとエラーになる。そもそもORDER BY句が不要
  • PostgreSQLの場合、FORMAT関数ではパーセント(%)をエスケープで2つ(%%)にする必要がある
  • PostgreSQLの場合、FROMとテーブル名の区切りが全角空白だとエラーになるので半角空白にする。

PL/SQL移行

PL/SQLをPostgreSQLに移行する ～移行概要/移行例～

製造・単体テストの進め方について

OracleからPostgresSQL移行ツール

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

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

セットアップ済みの Docker コンテナを利用する。

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

ソースからコンパイルするとなると敷居が高い(プリコンパイル済みを公開してくれるユーザーがいるので恩恵を受ける)、最初から使用できる環境があると使用しやすい。

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)

参照

最後に

まだエクスポートやインポートやチューニングやバックアップとリストアなどがあるのですが、また別の機会にしておきます。別記事で書いたのがあるのでリンクしておきます。

67
59
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
67
59

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?