OracleとPostgreSQLの違い

OracleからPostgreSQLへやってきた方へ
PostgreSQLにはデータベースクラスタという概念があります
PostgreSQLはユーザとスキーマを分けて考える必要があります
PostgreSQLにはsynonymが存在しません
PostgreSQLではユーザとロールはほぼ同義です。
PostgreSQLにはOracleのようなfrom dualはありません
PostgreSQLはDDLもロールバックする事ができます
PostgreSQLのストアドプログラムは「呼び出しユーザの権限」で動作します
PostgreSQLにはパッケージという概念がありません。
PostgreSQLではテーブル名、カラム名を内部では小文字で保持します
PostgreSQLでのDATE型には「日付」（精度１日）のみで時刻は入りません
PostgreSQLの表領域はクラスター単位で管理されます
PostgreSQLではスキーマに対しデフォルト表領域を割り当てる事はできません
PostgreSQLには機能として自立型トランザクションが存在しません
PostgreSQLには不可視列（invisible column）が存在しません
PostgreSQLではテーブルやインデックスの作成日時を取得できません
PostgreSQLのdecode関数はOracleのものとは全く異なります
PostgreSQLの文字列連結時の動作が異なります
PostgreSQLにはNVL関数が存在しません
PostgreSQLで”（連続するシングルクォーテーション）はNULLと扱いません
PostgreSQLでリテラルによる割り算は小数点以下が切り捨てられます
PostgreSQLにはOracleの共有プールに相当する「エリア」が存在しない
pgAdmin4がなんだか使い辛い

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

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

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

PostgreSQLは１つのインスタンスに複数のDBを作成する事ができます。この複数のデータベースをデータベースクラスタと呼びます。Oracleの場合１つのインスタンスからは必ず１つのDBしか扱えません。（１つのDBを複数のインスタンスからアクセスする事はあります（RAC））同じデータベースクラスタ内であっても、異なるDBに存在するテーブルの結合や、異なるDBを跨いでのinsert & select は出来ません。これらを実現するには、Oracleと同様に「DBリンク」を使って実現する事になります。（せっかく同じインスタンスからアクセスするのだから、もっと簡単でできればよいのに、、とよく感じます）

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

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

PostgreSQLにはsynonymが存在しません

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

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

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

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

PostgreSQLにはOracleのようなfrom dualはありません。また、どんな意味があるか分かりませんがPostgreSQLの場合『select from ex01』というようにselectする項目を未記入でもパーサに叱られる事はありません。selectされる条件に合うデータがあってもこの場合結果セットの件数は常に０件です。存在確認には使えるかもしれませんね。

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の表領域はクラスター単位で管理されます

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

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

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

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テキストを拾うにはログファイルへ落として確認するか、バージョン13以降に限定ですがpg_stat_statements拡張モジュールを追加して確認するしかありません。

pgAdmin4がなんだか使い辛い

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