Guides Oracle Database to PostgreSQL migration — the most common DB migration in Japan driven by Oracle licensing costs. Use when planning Oracle 11g/12c/19c to PostgreSQL migration, converting Oracle-specific SQL (ROWNUM, CONNECT BY, NVL, DECODE, SYSDATE) to PostgreSQL equivalents, migrating PL/SQL stored procedures to PL/pgSQL or application layer, assessing Oracle partitioning and sequences migration, or producing a migration plan document. Covers data type mapping, SQL syntax differences, stored procedure conversion, and tool selection (pgloader, ora2pg, AWS SCT).
-- Oracle
SELECT * FROM employee WHERE ROWNUM <= 10;
SELECT * FROM (
SELECT e.*, ROWNUM rn FROM employee e ORDER BY emp_id
) WHERE rn BETWEEN 11 AND 20;
-- PostgreSQL
SELECT * FROM employee LIMIT 10;
SELECT * FROM employee ORDER BY emp_id LIMIT 10 OFFSET 10;
CONNECT BY（階層クエリ）→ WITH RECURSIVE
-- Oracle
SELECT emp_id, manager_id, emp_name, LEVEL
FROM employee
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;
-- PostgreSQL
WITH RECURSIVE emp_tree AS (
SELECT emp_id, manager_id, emp_name, 1 AS level
FROM employee WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id, e.emp_name, t.level + 1
FROM employee e JOIN emp_tree t ON e.manager_id = t.emp_id
)
SELECT * FROM emp_tree;
DECODE → CASE WHEN
-- Oracle
SELECT DECODE(status, 'A', '有効', 'I', '無効', '不明') FROM orders;
-- PostgreSQL
SELECT CASE status WHEN 'A' THEN '有効' WHEN 'I' THEN '無効' ELSE '不明' END FROM orders;
NVL / NVL2 → COALESCE / CASE
-- Oracle
SELECT NVL(commission, 0), NVL2(commission, salary + commission, salary) FROM emp;
-- PostgreSQL
SELECT COALESCE(commission, 0),
CASE WHEN commission IS NOT NULL THEN salary + commission ELSE salary END
FROM emp;
-- Oracle
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
INSERT INTO log (created_at) VALUES (SYSDATE);
-- PostgreSQL
SELECT NOW(), CURRENT_TIMESTAMP; -- DUAL テーブル不要
INSERT INTO log (created_at) VALUES (NOW());
DUAL テーブル
-- Oracle
SELECT 1 FROM DUAL;
SELECT SEQ_NAME.NEXTVAL FROM DUAL;
-- PostgreSQL: DUAL 不要
SELECT 1;
SELECT NEXTVAL('seq_name');
シーケンス
-- Oracle
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
SELECT emp_seq.NEXTVAL FROM DUAL;
INSERT INTO employee (id, name) VALUES (emp_seq.NEXTVAL, 'Tanaka');
-- PostgreSQL
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
SELECT NEXTVAL('emp_seq');
INSERT INTO employee (id, name) VALUES (NEXTVAL('emp_seq'), 'Tanaka');
-- または IDENTITY カラム (PostgreSQL 10+)
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
MERGE → INSERT ... ON CONFLICT
-- Oracle
MERGE INTO target t USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);
-- PostgreSQL
INSERT INTO target (id, value) VALUES (...)
ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value;
Phase 3: ストアドプロシージャ移行
移行判断マトリクス
PL/SQL パターン
移行先
難易度
単純な DML ロジック
PL/pgSQL
低
カーソル処理
PL/pgSQL CURSOR
中
UTL_FILE（ファイルI/O）
アプリ層（Java/TS）へ移管
高
DBMS_SCHEDULER（ジョブ）
pg_cron / アプリ cron
中
DBMS_OUTPUT.PUT_LINE
RAISE NOTICE
低
Oracle パッケージ（PACKAGE）
PostgreSQL スキーマ分割
中
動的SQL（EXECUTE IMMEDIATE）
EXECUTE ... USING
中
PL/SQL → PL/pgSQL 変換例
-- Oracle PL/SQL
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id IN NUMBER, p_amount IN NUMBER) AS
v_current NUMBER;
BEGIN
SELECT salary INTO v_current FROM employee WHERE emp_id = p_emp_id;
IF v_current IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, '社員が存在しません');
END IF;
UPDATE employee SET salary = salary + p_amount WHERE emp_id = p_emp_id;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20002, 'レコードなし');
END;
-- PostgreSQL PL/pgSQL
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id BIGINT, p_amount NUMERIC)
LANGUAGE plpgsql AS $$
DECLARE
v_current NUMERIC;
BEGIN
SELECT salary INTO v_current FROM employee WHERE emp_id = p_emp_id;
IF v_current IS NULL THEN
RAISE EXCEPTION '社員が存在しません' USING ERRCODE = 'P0001';
END IF;
UPDATE employee SET salary = salary + p_amount WHERE emp_id = p_emp_id;
-- PostgreSQL は自動コミット / BEGIN...COMMIT は呼び出し側で管理
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'レコードなし' USING ERRCODE = 'P0002';
END;
$$;