Oracle 常用函数与 PostgreSQL 对照表
在从 Oracle 向 PostgreSQL 迁移时，经常会遇到函数不兼容的问题。下面整理了常见的函数及其替代方案。
1. 条件判断类
| Oracle | PostgreSQL 对应 | 说明 |
|---|
DECODE(expr, val1, res1, val2, res2, ..., default) | CASE WHEN expr = val1 THEN res1 WHEN expr = val2 THEN res2 ELSE default END | DECODE 是 Oracle 特有语法，PostgreSQL 用 CASE 实现。 |
NVL(expr1, expr2) | COALESCE(expr1, expr2) | 返回第一个非空值。 |
NVL2(expr1, expr2, expr3) | CASE WHEN expr1 IS NOT NULL THEN expr2 ELSE expr3 END | 判断是否为空，返回不同结果。 |
NULLIF(expr1, expr2) | NULLIF(expr1, expr2) | 两个值相等时返回 NULL，PostgreSQL 支持，语法相同。 |
2. 字符串处理
| Oracle | PostgreSQL 对应 | 说明 |
|---|
|| (字符串拼接) | || | 两边一致。 |
SUBSTR(str, start, len) | SUBSTRING(str FROM start FOR len) 或 SUBSTRING(str, start, len) | 语法略有不同。 |
INSTR(str, substr, start, occurrence) | POSITION(substr IN str)（简单情况） 或 STRPOS(str, substr) | Oracle 更复杂，需要自己组合实现。 |
LPAD(str, len, padstr) | LPAD(str, len, padstr) | PostgreSQL 也支持。 |
RPAD(str, len, padstr) | RPAD(str, len, padstr) | PostgreSQL 也支持。 |
TRIM(both/leading/trailing 'x' from str) | TRIM(both/leading/trailing 'x' from str) | 两边一致。 |
3. 数值处理
| Oracle | PostgreSQL 对应 | 说明 |
|---|
ROUND(number, decimals) | ROUND(number, decimals) | 一致。 |
TRUNC(number, decimals) | TRUNC(number, decimals) | PostgreSQL 也支持。 |
MOD(a, b) | MOD(a, b) | 一致。 |
CEIL(expr) | CEIL(expr) 或 CEILING(expr) | PostgreSQL 支持。 |
FLOOR(expr) | FLOOR(expr) | 一致。 |
4. 日期处理
| Oracle | PostgreSQL 对应 | 说明 |
|---|
SYSDATE | CURRENT_TIMESTAMP 或 NOW() | 返回当前时间。 |
SYSTIMESTAMP | CURRENT_TIMESTAMP | 一致。 |
ADD_MONTHS(date, n) | date + (n || ' month')::interval | 使用 interval 实现。 |
MONTHS_BETWEEN(d1, d2) | EXTRACT(YEAR FROM age(d1,d2))*12 + EXTRACT(MONTH FROM age(d1,d2)) | PostgreSQL 没有内置，需要组合函数。 |
TRUNC(date, 'MM') | DATE_TRUNC('month', date) | 对齐到月。 |
TRUNC(date, 'YYYY') | DATE_TRUNC('year', date) | 对齐到年。 |
NEXT_DAY(date, 'MONDAY') | date + ((dow + 7 - EXTRACT(DOW FROM date)) % 7) | PostgreSQL 需要手动实现。 |
5. 类型转换
| Oracle | PostgreSQL 对应 | 说明 |
|---|
TO_CHAR(date/number, format) | TO_CHAR(date/number, format) | 一致。 |
TO_DATE(str, format) | TO_DATE(str, format) | 一致。 |
TO_NUMBER(str, format) | TO_NUMBER(str, format) | 一致。 |
CAST(expr AS type) | CAST(expr AS type) 或 expr::type | PostgreSQL 更灵活。 |
6. 唯一标识符生成
| Oracle | PostgreSQL 对应 | 说明 |
|---|
SYS_GUID() | gen_random_uuid() (pgcrypto) 或 uuid_generate_v4() (uuid-ossp) | Oracle 返回 RAW(16)，PostgreSQL 返回 UUID 类型，需要启用扩展。 |
示例：
-- pgcrypto 扩展
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
SELECT gen_random_uuid();
-- uuid-ossp 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
7. 聚合与集合
| Oracle | PostgreSQL 对应 | 说明 |
|---|
LISTAGG(col, ',') WITHIN GROUP (ORDER BY col) | STRING_AGG(col, ',' ORDER BY col) | 用 STRING_AGG 替代。 |
ROWNUM | LIMIT n 或 ROW_NUMBER() OVER() | PostgreSQL 没有 ROWNUM，用窗口函数或限制行数。 |
RANK() OVER(...) | RANK() OVER(...) | 一致。 |
DENSE_RANK() OVER(...) | DENSE_RANK() OVER(...) | 一致。 |
总结
- 条件函数：
DECODE → CASE，NVL → COALESCE。
- 字符串函数：大部分兼容，
INSTR 需要改成 STRPOS/POSITION。
- 日期函数：PostgreSQL 用
DATE_TRUNC、INTERVAL 替代 Oracle 的日期操作。
- 聚合函数：
LISTAGG → STRING_AGG。
- 分页与行号：PostgreSQL 用
LIMIT/OFFSET 和 窗口函数替代 ROWNUM。
