OracleからPostgreSQLへの移行を行なっている中で、差分表が欲しいなと思ったので備忘録。
今後も適宜追加予定。
1. OracleとPostgreSQLの対応表（主要な関数）
よく使う名前や引数が異なる関数の対応一覧
| 分類 | Oracle関数 | PostgreSQL関数 | 備考 |
|---|---|---|---|
| 文字列 | NVL(ex1, ex2) |
COALESCE(ex1, ex2) |
PostgreSQLではNVLは使えない |
INSTR(s, sub) |
STRPOS(s, sub) |
PostgreSQLは引数の順序が逆になる場合があるため注意 | |
SUBSTR(s, p, l) |
SUBSTRING(s, p, l) |
||
LISTAGG(c, ',') |
STRING_AGG(c, ',') |
集約関数の記述ルールが異なる | |
| 数値 | TRUNC(num) |
TRUNC(num) |
基本同じだけど、日付のTRUNCは挙動が異なる |
MOD(n1, n2) |
n1 % n2 または MOD(n1, n2)
|
||
| 日付 | SYSDATE |
CURRENT_TIMESTAMP |
PostgreSQLでは now() も一般的 |
ADD_MONTHS(d, n) |
d + INTERVAL 'n months' |
PostgreSQLはインターバル演算を使用する | |
LAST_DAY(d) |
なし (関数作成が必要) | (date_trunc('month', d) + interval '1 month - 1 day')::date |
|
| 変換 | TO_CHAR(num, fmt) |
TO_CHAR(num, fmt) |
書式モデル（FM等）に一部差異あり |
DECODE(v, s, r, d) |
CASE WHEN ... END |
PostgreSQLにはDECODE（条件分岐用）はない |
2. Oracle特有の関数とPostgreSQLでの代替
Oracleに標準実装されており、PostgreSQLで利用する場合は標準SQLや演算子で書き換える必要があるもの
| Oracle関数 | PostgreSQLでの実現方法 | 内容 |
|---|---|---|
NVL2(e1, e2, e3) |
CASE WHEN e1 IS NOT NULL THEN e2 ELSE e3 END |
e1がNULLかどうかで戻り値を分ける。 |
SYSTIMESTAMP |
CURRENT_TIMESTAMP |
タイムゾーン付きの現在時刻 |
DUMP(expr) |
なし | データ型やバイト長を確認するデバッグ用 |
VSIZE(expr) |
pg_column_size() |
格納されているバイトサイズを返す |
RAWTOHEX(r) |
ENCODE(r, 'hex') |
バイナリを16進数文字列に変換 |
3. PostgreSQL特有の関数
Oracleには存在しない、あるいはPostgreSQL特有の便利な関数
| PostgreSQL関数 | 内容 | Oracleでの対応 |
|---|---|---|
GEN_RANDOM_UUID() |
UUIDを生成する。 |
SYS_GUID() が近い |
GENERATE_SERIES() |
連続した数値を生成する（集合を返す）。 |
CONNECT BY LEVEL 等で代用が必要 |
DATE_PART() |
日付から年や月を数値で抽出する。 |
EXTRACT() が標準SQLとして両方で利用可 |
REGEXP_REPLACE() |
正規表現置換（POSIX版）。 | Oracleにもあるが、フラグの指定方法が異なる |
ARRAY_AGG() |
カラムの値を配列にまとめる。 |
COLLECT に近いが使い勝手が異なる |
その他注意点
-
空文字とNULL: Oracleは空文字
''をNULLとして扱うが、PostgreSQLでは明確に区別される -
TRUNC関数の引数: Oracleの
TRUNC(date)は日付の切り捨てによく使われるが、PostgreSQLのTRUNCは数値用であるため、日付にはDATE_TRUNC('day', timestamp)を使用する