OracleとPostgreSQLの違い

OracleからPostgreSQLへやってきた方へ

PostgreSQLを久しぶりにまた触る事になりました。普段使っているOracleと比べ違和感ある事を自分なりにメモってみました。OracleからPostgreSQLへのシステム移行を検討されている方、まさに移行の最中といった方の参考になればうれしいです。

PostgreSQLにはデータベースクラスタという概念があります

PostgreSQLは１つのインスタンスに複数のDBを作成する事ができます。この複数のデータベースをデータベースクラスタと呼びます。Oracleの場合１つのインスタンスからは必ず１つのDBしか扱えません。（１つのDBを複数のインスタンスからアクセスする事はあります（RAC））（2026年10月追記：これは従来の構成の話です。Oracle 12c からは「マルチテナント」という仕組みで、１つのインスタンス（CDB）の中に複数のDB（PDB）を作れるようになりました。21c 以降は、マルチテナントの構成しか使えないようです。この部分は未検証です。）同じデータベースクラスタ内であっても、異なるDBに存在するテーブルの結合や、異なるDBを跨いでのinsert & select は出来ません。これらを実現するには、Oracleと同様に「DBリンク」を使って実現する事になります。（せっかく同じインスタンスからアクセスするのだから、もっと簡単でできればよいのに、、とよく感じます）

PostgreSQLはユーザとスキーマを分けて考える必要があります

Oracleではスキーマとユーザは「ほぼ同じ」と考えて大丈夫とおもいます。さらにはスキーマをオーナーと認識したほうが良いところもあります。（create userのタイミングで内部的には同じスキーマ名が作成されているようなので）本来スキーマとは名前空間を表していて、ユーザとは言葉の通りDB利用者という事ですがOracleではスキーマ名とユーザ名が常に同じとなるのでこれら区別をしなくとも特に問題はないのだと思います。これに対し、PostgreSQLではユーザ名が必ず同じスキーマ名に割り当てられるとは限らないという事です。デフォルトでは全てのユーザは、publicスキーマが割り当てられますがこれはOracleには存在しない機能です。PostgreSQLではユーザ名と同じ名前のスキーマを作成すると、publicスキーマより優先して参照するようになっています。（スキーマ検索パス（search_path）という機能によるものです）さらには、Oracleと異なりオーナー≒スキーマとはなりません。同一スキーマ内に異なるオーナーのオブジェクトを配備できます。Oracleではありえない事です。
最も大きく異なるのは、PostgreSQLのユーザは「データベースクラスタ」内全体で共通ですが、スキーマはDB毎に分けて管理されています。

（2026年10月追記） PostgreSQL 15 からは、public スキーマにテーブルなどを作る権限（CREATE）が、データベースの所有者以外のユーザにはなくなりました。15 以降へ移行すると、一般のユーザで public スキーマにテーブルを作ろうとして「permission denied for schema public」になることがあります。その場合は、次のどちらかで対応します。

-- public スキーマに作る権限を付ける
GRANT CREATE ON SCHEMA public TO ユーザ名;

-- または、ユーザ名と同じ名前のスキーマを作って使う（search_path で public より優先される）
CREATE SCHEMA ユーザ名 AUTHORIZATION ユーザ名;

実際にエラーになったときの記録と、権限の読み方は「PostgreSQL 15 から public スキーマにテーブルを作れない（permission denied for schema public）」にまとめています。

PostgreSQLにはsynonymが存在しません

意外にもPostgreSQLにはOracleのシノニムに相当する機能は存在しません。しかしながらPostgreSQLには、「カレントスキーマ」という概念と、スキーマ修飾を省略し各種オブジェクトを指定した場合、「スキーマ検索パス（search_path）」に従って、オブジェクトを該当させる仕組みがあります。こちらをうまく使って代替えする事はできます。（全く同じ要件を実現できるわけではないので注意は必要です。）

PostgreSQLではユーザとロールはほぼ同義です。

ロールに「ログインできる権限(注1)を付与したもの」をユーザと認識しておけば良いとおもいます。また「スキーマ検索パス（サーチパス）」の指定はユーザに対して行う事もロールと異なる点です。（注1） 正式には「LOGIN属性」と呼びます

PostgreSQLにはOracleのようなfrom dualはありません

PostgreSQLにはOracleのようなfrom dualはありません 。また、どんな意味があるか分かりませんがPostgreSQLの場合『select from ex01』というようにselectする項目を未記入でもパーサに叱られる事はありません。この場合、列が1つもない行が、条件に合うデータの件数分返ってきます（3行あるテーブルなら3行）。そのため、EXISTS (SELECT FROM ex01 WHERE c1 = 2) のような存在確認の書き方としてよく使われます。

-- PostgreSQL 15 で確認（ex01 は3行のテーブル）
SELECT FROM ex01;
--
-- (3 行)

SELECT FROM ex01 WHERE c1 = 2;
--
-- (1 行)

SELECT EXISTS (SELECT FROM ex01 WHERE c1 = 2);
--  exists
-- --------
--  t

PostgreSQLはDDLもロールバックする事ができます

デフォルトでの動作となりますがOracleは自動コミットOFFに対し、PostgreSQLは自動コミットONです。Oracleに合わせ、PostgreSQLで自動コミットオフにした状態で利用すると次の点で戸惑います。OracleはDDL発行タイミングで強制的にコミットが入るため、例えばcreate tableを実行したタイミングでそれまで実行しているDMLの内容がコミットされる事はもちろん、作成したテーブルも当然コミットされます。これに対しPostgreSQLはDDL発行してもコミットはされません。つまりDDL発行した後もロールバックする事ができます。（ truncate table もロールバックできます）この動きはOracleから入ってきた方はかなり戸惑うのではないでしょうか。このあたりの動作はちょっとクセがありそうなので別途検証ページ書きたいと思っています。

PostgreSQLのストアドプログラムは「呼び出しユーザの権限」で動作します

PostgreSQLは「ストアドプログラムを呼び出したユーザの権限で実行（SECURITY INVOKER）」がデフォルトです。「ストアドプログラムの所有者（作成したユーザ）の権限で実行」させるにはプログラムにSECURITY DEFINER　と記載する必要があります。これに対しOracleは「ストアドプログラムの所有者（作成したユーザ）の権限で実行（AUTHID DEFINER）」がデフォルトで、呼び出したユーザの権限で実行させる場合は AUTHID CURRENT_USER　と記載する必要があります。

PostgreSQLにはパッケージという概念がありません。

Oracleでは複数のプロシージャやファンクションを一つのパッケージとしてまとめる事ができますが、PostgreSQLではできません。私の場合Oracleでパッケージを利用する最も大きな理由はネームスペースが広がる事です。数多くのプロシージャやファンクションを作成する場合、また汎用的なプログラムを作成する際、名前の衝突が発生しがちですがパッケージを使う設計にする事でこれら解決できます。これだけなら、PostgreSQLでもスキーマを使えばなんとかなりそうですが、パッケージ内のプロシージャで共有できる「パッケージ変数」については代替の手段がないとおもいます。

PostgreSQLではテーブル名、カラム名を内部では小文字で保持します

Oracleは小文字でテーブルを作成しても内部では大文字で保持していますがPostgreSQLはそれが逆です。 当然Oracleにおいても create table “ex01″(c1 number) というようにダブルクォーテーションで囲むと小文字でテーブルを作成する事はできます。この点はPostgreSQLも同様です。

PostgreSQLでのDATE型には「日付」（精度１日）のみで時刻は入りません

Oracle、PostgreSQL共に、DATE型、TIMESTAMP型存在しますが、OracleのDATE型では秒単位まで表現できますがPostgreSQLは「日」までで時刻は表現できません。時刻まで保持するにはTIMESTAMPを使う事になります。個人的にはPostgreSQLの仕様のほうが良いと感じます。Oracleを使ったシステムでは「日にち単位」までを表現するのにVARCHAR2(8)やCHAR(8)で定義している箇所多くみますがこれはDATE型に秒まで入ってしまう事によると思います。（このためわざわざ日付としてのチェックが必要な場合も出てきてしまいます）

PostgreSQLの now() と CURRENT_TIMESTAMP はトランザクション開始時刻を返します

（2026年10月追記） Oracle の SYSDATE・SYSTIMESTAMP・CURRENT_TIMESTAMP は「SQL文を実行した時刻」ですが、PostgreSQL の now() と CURRENT_TIMESTAMP は「トランザクションを始めた時刻」を返し、COMMIT するまで同じ値です。移行で SYSDATE を now() に置き換えると、長いトランザクション（バッチやストアド）で動きが変わります。Oracle と同じ「SQL文の時刻」がほしいときは statement_timestamp() を使います。詳しくは「Oracle と PostgreSQL の移行で気をつけたい時刻関数の違い（両方向）」にまとめています。

PostgreSQLの表領域はクラスター単位で管理されます

Oracleではデータベース単位で表領域を作成しますが、PostgreSQLではデータベースクラスタ単位で管理（作成）されます。またPostgreSQLの表領域にはオーナが存在します。

PostgreSQLではスキーマに対しデフォルト表領域を割り当てる事はできません

Oracleではスキーマに対し「デフォルト表領域」を自由に割り当てる事ができますが、PostgreSQLの場合、データベースに対してデフォルト表領域が用意されています。つまりテーブルやインデックスを作成する際表領域の指定をしない限り、データベース毎のデフォルト表領域へ作成されます。（表領域のオーナであるユーザにて「表領域を指定せずテーブルを作成しても」とオーナとなっている表領域に作成される事はありません。データベースのデフォルト表領域に作成されます。個人的に納得できないところです、、）

PostgreSQLには機能として自律型トランザクションが存在しません

Oracleには自律型トランザクション (PRAGMA AUTONOMOUS_TRANSACTION)といって、メインのトランザクションとは別に独自のトランザクションを作成する機能が存在しますが、PostgreSQLには「機能として」は存在しません。拡張モジュールとして用意されているdblinkを使ってこれを実装する事になります。（別途解説を設けたいと考えていますが、dblink拡張モジュールを使いリモートDBへ更新を行った場合ローカル側トランザクションとは別で、すぐさまcommitが入ります。この動きが自律型トランザクションを実現する事になっています。見方を変えると、OracleのDBリンクと異なりリモート側更新をローカル側のトランザクションとして管理できないのです）

PostgreSQLには不可視列（invisible column）が存在しません

Oracleには12cから不可視列（invisible column）が利用できるようになっていますが、PostgreSQLには存在しません。MySQL(Ver.8から？）にも「非表示カラム」として実装されているので、PostgreSQLにもそろそろしてくれてもいいのですけれど、、

PostgreSQLではテーブルやインデックスの作成日時を取得できません

Oracleでは各種オブジェクトの作成日時は(例としてdba_objects)簡単に取得する事できますが、PostgreSQLではそれができません。

PostgreSQLのdecode関数はOracleのものとは全く異なります

Oracleでよく利用されているdecode関数はcase文に代わる働きをしていますが、PostgreSQLではテキストで渡された値をデコードしバイナリデータで返却する動きとなりOracleとは全く異なるものとなっています。

PostgreSQLの文字列連結時の動作が異なります

Oracleでの文字列連結は、concat()関数とパイプ(||)を使った連結方法あります。Oracleの場合何れの連結方法においてもNULLが混じった場合NULLは無視して連結をしますが、PostgreSQLのパイプによる連結は一つでもNULLが混じると結果もNULLになる点注意が必要です。また、OracleにおいてNULL同士の連結はNULLになりますが、PostgreSQLのconcat()関数では空文字になります。

PostgreSQL 文字列連結方法と動作確認

concat()関数と||（２つのパイプ）を使った連結では動作が異なりますconcat()関数での連結ではNULLが混在した場合NULLを無視しますが、||（パイプ）での連結では１つでもNULLが混じると連結結果もNULLになります。参考ま...

oha-yo.com

2022.12.04

PostgreSQLにはNVL関数が存在しません

PostgreSQLにはNVL関数が存在しないので、COALESCE関数やCASE文で対応する必要があります。

postgreSQLにはNVLがありません

NVLとCOALESCEとCASE文今日後輩が、「PostgreSQLにNVLないんですかぁ？」と聞いてきたのですが、、、ありません。NVLはOracle独自の関数です。（実は私も、PostgreSQLに移ってきた時には戸惑いました）検証デ...

oha-yo.com

2023.05.19

PostgreSQLで”（連続するシングルクォーテーション）はNULLと扱いません

これはOracleでの動きが他と比べてイレギュラーなのだと思いますが、以下Oracleでは両方とも「nullです」となりますが、PostgreSQLではc1=10のレコードに関しては「nullではありません」となります。

insert into ex300(c1,c2) values(10,'');
insert into ex300(c1,c2) values(20,null);

select c1,case when c2 is null then 'nullです'
else 'nullではありません'
end "判定"
from ex300 order by c1;

myposdb=# insert into ex300(c1,c2) values(10,'');
INSERT 0 1
myposdb=# insert into ex300(c1,c2) values(20,null);
INSERT 0 1
myposdb=# select c1,case when c2 is null then 'nullです'
myposdb-# else 'nullではありません'
myposdb-# end "判定"
myposdb-# from ex300 order by c1;
 c1 |        判定
----+--------------------
 10 | nullではありません
 20 | nullです
(2 行)

myposdb=#

PostgreSQLでリテラルによる割り算は小数点以下が切り捨てられます

--Oracle
SQL> select 10/3 as result from dual;

    RESULT
----------
3.33333333

-- PostgreSQL
myposdb=# select 10/3 as result;
 result
--------
      3
-- これを解決するにはキャストが必要です
-- 以下例では分子側をキャストしていますが分母側のキャストでも可能です。
-- 但し結果に対してキャストでは無意味です。
myposdb=# select cast(10 as numeric)/3 as result;
       result
--------------------
 3.3333333333333333
(1 row)

PostgreSQLにはOracleの共有プールに相当する「エリア」が存在しない

Oracleでは実行したSQLがしばらく共有プールに残っているのでここをv$sqlやv$sqlstats、v$sqltextにて参照すれば簡単にSQLテキストを確認できるのですが、そもそもPostgreSQLにはOracleの共有プールに相当する「エリア」が存在せず、デフォルトで過去実行SQLテキストを簡単に確認する事ができません。PostgreSQLで実行したSQLテキストを拾うにはログファイルへ落として確認するか、pg_stat_statements拡張モジュールを追加して確認するしかありません。

PostgreSQL 実行SQLの統計情報を取得する

pg_stat_statementspg_stat_statements拡張モジュールをインストールする事で、サーバで実行されたすべてのSQL文のプラン生成時と実行時の統計情報を取得できるようになります。PostgreSQLにおいてSQLの...

oha-yo.com

2024.02.12

pgAdmin4がなんだか使い辛い

OracleのSQL DeveloperとかEnterprise Managerに相当するものでしょうか、NativeのPostgreSQLの管理ツールです。見た目は美しいと思ったんですがねぇ、、どうしても個人的に使い辛いと感じたのでできる範囲で設定を好みに変更してみました

pgAdmin4の設定（日本語化他）

pgAdmin4を初めて使った際、正直使い辛かったです。個人的にですが、デフォルトの設定ではどうしても我慢ならず設定変更をした点は以下です。

oha-yo.com

2021.11.28