DB更改プロジェクト着任前の事前学習資料
| 項目 | Oracle | PostgreSQL |
|---|---|---|
| 種別 | 商用RDBMS | OSS（無償） |
| ライセンス | 非常に高額 | 無償（PostgreSQL License） |
| シェア | エンタープライズで最大手 | OSS DBで最大手 |
| SQL標準準拠 | 独自拡張が多い | SQL標準に忠実 |
| サポート | Oracle社による商用サポート | コミュニティ＋商用サポート選択 |
-- '' は NULL と同じ WHERE col = '' -- → 結果0件（NULLは=で比較不可） WHERE col IS NULL -- → 空文字も含めてヒット
-- '' は '' (空文字) のまま WHERE col = '' -- → 空文字のみヒット WHERE col IS NULL -- → NULLのみヒット（空文字は含まない）
| Oracle | PostgreSQL | 用途 |
|---|---|---|
NVL(a, b) | COALESCE(a, b) （コアレス） | NULL置換 |
NVL2(a,b,c) | CASE WHEN a IS NOT NULL THEN b ELSE c END | NULL条件分岐 |
DECODE(col,v1,r1,...) | CASE WHEN col=v1 THEN r1... END | 条件分岐 |
SYSDATE | NOW() / CURRENT_TIMESTAMP | 現在日時 |
TRUNC(date) | DATE_TRUNC('day', ts) | 日付の切り捨て |
TO_CHAR(d,'YYYYMMDD') | TO_CHAR(d,'YYYYMMDD') | 日付フォーマット（同じ） |
SUBSTR(s,1,5) | SUBSTRING(s,1,5) | 部分文字列 |
INSTR(s,'x') | POSITION('x' IN s) | 文字位置 |
MONTHS_BETWEEN(d1,d2) | EXTRACT(MONTH FROM AGE(d1,d2)) （エクストラクト） | 月差 |
CONNECT BY | WITH RECURSIVE （リカーシブ） | 階層クエリ |
SELECT * FROM ( SELECT *, ROWNUM rn FROM t WHERE ROWNUM <= 20 ) WHERE rn >= 11;
SELECT * FROM t LIMIT 10 OFFSET 10; -- シンプル！
| 項目 | Oracle | PostgreSQL |
|---|---|---|
| 外部結合 | WHERE a.id = b.id(+)Oracle独自構文 |
LEFT JOIN b ON a.id = b.idANSI標準 |
| DUALテーブル | SELECT 1 FROM DUAL |
SELECT 1FROM句不要 |
| 識別子の大小文字 | 大文字に正規化 COL / col / Col → すべて COL |
小文字に正規化 COL / col / Col → すべて col |
| 大小文字を区別したいとき | "Col"（ダブルクォート） |
"Col"（同様。ただし通常は避ける） |
| Oracle型 | PostgreSQL型 | 注意点 |
|---|---|---|
VARCHAR2(n) | VARCHAR(n) | ほぼ同じ |
CHAR(n) | CHAR(n) | 同じ |
NUMBER(p,s) | NUMERIC(p,s) | ほぼ同じ |
NUMBER(n)（整数） | INTEGER / BIGINT | 整数ならINTEGERが推奨 |
DATE | TIMESTAMP | ⚠️ OracleのDATEは時刻を含む。PGのDATEは日付のみ |
TIMESTAMP | TIMESTAMP | 同じ |
CLOB | TEXT | PGのTEXTは上限なし |
BLOB | BYTEA | バイナリ型 |
| なし | BOOLEAN | PGはBOOLEAN型あり（OracleはNUMBER(1)で代用） |
ROWID | なし | 代替：OID（非推奨）or シーケンスID |
| 項目 | Oracle | PostgreSQL |
|---|---|---|
| スキーマ | ユーザー = スキーマ USER_A を作るとスキーマも自動生成 | ユーザー ≠ スキーマ スキーマは別途作成。デフォルトは public |
| 接続設定 | listener.ora / tnsnames.ora | pg_hba.conf / postgresql.conf |
| インスタンス | 1インスタンス = 1DB | 1クラスタ内に複数DB可 |
| ストアド言語 | PL/SQL | PL/pgSQL（構文が似ているが差異あり） |
| バックアップ | RMAN | pg_dump / pg_basebackup |
| 統計情報収集 | DBMS_STATS（手動 or 夜間自動） | ANALYZE（AUTOVACUUM が自動実行） |
| 項目 | PL/SQL（Oracle） | PL/pgSQL（PostgreSQL） |
|---|---|---|
| 標準出力 | DBMS_OUTPUT.PUT_LINE('msg') | RAISE NOTICE 'msg' |
| カーソル定義 | CURSOR c IS SELECT ... | CURSOR c FOR SELECT ... |
| カーソル終端チェック | c%NOTFOUND | NOT FOUND |
| 無名ブロックの実行 | BEGIN ... END; / | DO $$ BEGIN ... END; $$; |
| 型参照 | table.col%TYPE | table.col%TYPE（同じ） |
| 例外処理 | EXCEPTION WHEN OTHERS | EXCEPTION WHEN OTHERS（同じ） |
DECLARE
v_name VARCHAR2(100) := 'テスト';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
DO $$ DECLARE v_name VARCHAR(100) := 'テスト'; BEGIN RAISE NOTICE '%', v_name; END; $$;
DECLARE
CURSOR c1 IS
SELECT name FROM users;
v_name users.name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE c1;
END;
/
DO $$
DECLARE
c1 CURSOR FOR
SELECT name FROM users;
v_name users.name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_name;
EXIT WHEN NOT FOUND;
RAISE NOTICE '%', v_name;
END LOOP;
CLOSE c1;
END;
$$;
BEGIN SELECT name INTO v_name FROM users WHERE id = 1; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('なし'); WHEN OTHERS THEN RAISE; END; /
BEGIN SELECT name INTO v_name FROM users WHERE id = 1; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE NOTICE 'なし'; WHEN OTHERS THEN RAISE; END;
DO $$ ... $$; で囲む。Oracle の BEGIN ... END; / とは書き方が異なる。移植時に書き忘れやすいポイント。(+) 外部結合構文の使用 → LEFT/RIGHT JOINに書き換え必要ROWNUM の使用 → LIMIT/OFFSETに変換FROM DUAL の使用 → FROM句を削除NVL / DECODE → COALESCE / CASE WHENに変換CONNECT BY 階層クエリ → WITH RECURSIVEに変換DATE型に時刻が入っているカラム → TIMESTAMPに変換SYSDATE → NOW() / CURRENT_TIMESTAMPNUMBER型を整数として使っているカラムがないか| 項目 | Oracle | PostgreSQL |
|---|---|---|
| 自動採番 | CREATE SEQUENCE+ トリガーで NEXTVAL |
SERIAL（簡易）GENERATED AS IDENTITY（推奨） |
| 現在値取得 | seq.CURRVAL |
currval('seq_name') |
| 次の値取得 | seq.NEXTVAL |
nextval('seq_name') |
ja_JP.UTF-8 または C を選択。| 種類 | 用途 | 備考 |
|---|---|---|
B-tree | 通常の等値・範囲検索 | デフォルト。Oracleと同じ |
GIN | 全文検索・配列・JSONB | PG独自。LIKE検索にも使える |
GiST | 地理データ・範囲型 | PG独自 |
BRIN | 大規模テーブルの範囲検索 | 物理順序が整っている場合に有効 |
CREATE INDEX ON t (LOWER(col))等）。移行元にある場合は確認。EXPLAIN ANALYZEで実行計画を確認EXPLAIN PLAN FOR SELECT * FROM t; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 実行計画のみ EXPLAIN SELECT * FROM t; -- 実際に実行して詳細表示 EXPLAIN ANALYZE SELECT * FROM t;
EXPLAIN ANALYZE は実際にSQLを実行する。本番環境では SELECT のみで使うこと（UPDATE/DELETEにはトランザクションでロールバックを）。| 種類 | 説明 |
|---|---|
AUTOVACUUM | PostgreSQLが自動で実行。通常はこれに任せる。 |
VACUUM | 不要領域を回収（ディスク解放はしない） |
VACUUM FULL | テーブルを再構築してディスクを解放。テーブルロックが発生するので本番は要注意。 |
ANALYZE | 統計情報を更新。実行計画の精度が上がる。 |
pg_stat_user_tables で dead tupleの蓄積を監視する。ANALYZE テーブル名 を実行。| 方法 | 説明 | 用途 |
|---|---|---|
pg_dump | 論理バックアップ（SQL形式） | 特定テーブルのバックアップ、DBマイグレーション |
pg_dumpall | クラスタ全体の論理バックアップ | 全DB・ロール含めた完全バックアップ |
pg_basebackup | 物理バックアップ | ディザスタリカバリ、レプリカ構築 |
| WALアーカイブ | WALログを継続的に保存 | PITR（ポイントインタイムリカバリ） |
pg_basebackup + WALアーカイブ の組み合わせでPITRを構成するのが一般的。OracleのRMANに相当する構成。| ビュー名 | 内容 | Oracleの相当品 |
|---|---|---|
pg_stat_activity | 実行中のセッション・SQL | V$SESSION |
pg_stat_user_tables | テーブルのアクセス統計・dead tuple数 | DBA_TABLES |
pg_stat_user_indexes | インデックスの利用状況 | V$INDEX_STATS |
pg_locks | ロック待ち状況 | V$LOCK |
pg_stat_bgwriter | バックグラウンドライターの統計 | （類似なし） |
pg_stat_statements | SQLごとの実行統計（拡張機能） | V$SQL / AWR |
pg_stat_statements はデフォルト無効。postgresql.conf に shared_preload_libraries = 'pg_stat_statements' を追加して有効化する。スロークエリ分析に非常に有用。-- postgresql.conf に設定 log_min_duration_statement = 1000 -- 1秒以上のSQLをログ出力 -- pg_stat_statementsで確認（拡張機能有効化後） SELECT mean_exec_time, calls, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;
| ファイル | 役割 | Oracleの相当品 |
|---|---|---|
postgresql.conf | DB全体の設定（メモリ・ログ・接続数等） | init.ora / spfile |
pg_hba.conf | 接続認証の設定（IPアドレス・認証方式） | sqlnet.ora |
pg_ident.conf | OSユーザーとDBユーザーのマッピング | （類似なし） |
pg_hba.conf の設定ミスは接続不能に直結する。変更後は必ず pg_reload_conf() または pg_ctl reload で反映し、疎通確認をすること。| 項目 | Oracle | PostgreSQL |
|---|---|---|
| 接続モデル | 共有サーバー or 専用サーバー | 1接続 = 1プロセス |
| 最大接続数 | processes パラメータ | max_connections（デフォルト100） |
| 推奨構成 | （組み込み） | PgBouncer / pgpool-II を前段に配置 |
pg_upgrade またはダンプ＆リストアが必要