株式会社テイクーワンのK.Tです。

これまで携わったプロジェクトではOracleを使用する機会が多くありましたが、今回PostgreSQLを導入する機会がありました。そこで本記事では、OracleとPostgreSQLのSQLに関する主な違いを、メリット・デメリットにつながる観点も含めて整理します。

ライセンス

PostgreSQLは無償で利用できる一方、Oracleは一般的にライセンス費用が発生します。コスト面は、両者を比較する際の大きな違いの一つです。

SQLの基本文法

基本的なSQLの文法は共通する部分が多いものの、関数や一部の記述方法には違いがあります。

よく使用する関数・構文の違い

PostgreSQLでは「DUAL」テーブルが不要

日付・時刻

<Oracle>

select sysdate from DUAL; 
select systimestamp from DUAL; 

<PostgreSQL>

select now();
select current_timestamp;

計算

<Oracle>

select 1 + 2 from DUAL;

<PostgreSQL>

select 1 + 2;

NULL

ユーザーテーブルの名前（列名）がNULLの場合に「未設定」と表示する例

<Oracle>

select NVL(名前,'未設定') from ユーザーテーブル; 

<PostgreSQL>

select COALESCE(名前,'未設定') from ユーザーテーブル; 

名前に「さん」を連結する際、名前がNULLの場合

<Oracle>

select 名前 || 'さん' from ユーザーテーブル; 　→　結果は「さん」

<PostgreSQL>

select 名前 || 'さん' from ユーザーテーブル; 　→　結果はNULL

UPSERT

データが存在しない場合は登録し、存在する場合は更新する例

<Oracle>

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (s.id, s.name);

PostgreSQL

INSERT INTO target (id, name)
SELECT
    s.id,
    s.name
FROM source AS s
ON CONFLICT (id)
DO UPDATE
SET
    name = EXCLUDED.name;

外部結合

table_aとtable_bを外部結合する例

Oracle

①

FROM table_a
   LEFT OUTER JOIN table_b
    ON table_a.id = table_b.id;

または

②

FROM table_a,
     table_b
     WHERE table_a.id = table_b.id(+);

PostgreSQL

FROM table_a
LEFT OUTER JOIN table_b
    ON table_a.id = table_b.id;

PostgreSQLでは①の記述方法を使用します。②のようなOracle独自の外部結合演算子は簡潔に書ける一方、現在は標準的なJOIN構文を使用するケースが多いと思われます。

COMMIT・ROLLBACK

TRUNCATE TABLE

Oracle

暗黙的にコミットされるため、ロールバックできません。

PostgreSQL

トランザクション内で実行した場合はロールバック可能です。削除を確定するにはCOMMITが必要です。

トランザクション内で構文エラーが発生した場合

PostgreSQL

①update～ 実行（処理正常）
②select～ 実行（構文エラー）
③rollback が必要

ROLLBACKせずに再度SELECTやCOMMITを実行してもエラーになります。PostgreSQLでは、1つのエラーによってトランザクション全体が中断状態となるため、ROLLBACKまたはROLLBACK TO SAVEPOINTを実行しない限り、以降のSQLは実行できません。

一方、OracleではSQLの構文エラーが発生しても、通常はトランザクション全体が中断状態になることはありません。

SELECT時の件数制限

ユーザーテーブルからidの昇順で10件取得する例

Oracle（11g以前）

SELECT *
FROM (
    SELECT *
    FROM ユーザーテーブル
    ORDER BY id
)
WHERE ROWNUM <= 10;

PostgreSQL

SELECT *
FROM ユーザーテーブル
ORDER BY id
LIMIT 10;

Oracle 11g以前では、並び替えた結果から件数を制限する場合、サブクエリが必要となりSQLの階層が増えます。なお、Oracle 12c以降では、以下のようにFETCH FIRST句を使用できるようです。

SELECT *
FROM ユーザーテーブル
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

まとめ

OracleとPostgreSQLでは、一部の関数や構文、トランザクションの扱いに違いがあり、同じSQLでも結果や挙動が変わる場合があります。一方で、実現できる機能に大きな差は少なく、用途によってはPostgreSQLでも十分に対応できると感じました。特にコスト面を重視する場合、PostgreSQLは有力な選択肢になると思います。