Oracle to PostgreSQL: NVL, DECODE, and Null-Handling Functions

Problem

Oracle provides several functions — NVL, NVL2, DECODE — that have no direct equivalents in standard SQL but are commonly used in Oracle stored procedures and inline SQL. PostgreSQL uses standard SQL alternatives: COALESCE, NULLIF, and CASE expressions.

Behavior Comparison

NVL

Oracle: NVL(expr, replacement) — returns replacement if expr is NULL, otherwise expr.

PostgreSQL: Use COALESCE(expr, replacement) — semantically identical for two arguments.

-- Oracle
NVL(column_name, 'default')

-- PostgreSQL
COALESCE(column_name, 'default')

NVL2

Oracle: NVL2(expr, not_null_val, null_val) — returns not_null_val if expr IS NOT NULL, null_val if NULL.

PostgreSQL: No direct equivalent — use CASE:

-- Oracle
NVL2(column_name, 'has value', 'no value')

-- PostgreSQL
CASE WHEN column_name IS NOT NULL THEN 'has value' ELSE 'no value' END

DECODE

Oracle: DECODE(expr, search1, result1, search2, result2, ..., default) — equality-based switch.

PostgreSQL: No DECODE function — use CASE WHEN:

-- Oracle
DECODE(status, 1, 'Active', 2, 'Inactive', 'Unknown')

-- PostgreSQL
CASE status
  WHEN 1 THEN 'Active'
  WHEN 2 THEN 'Inactive'
  ELSE 'Unknown'
END

Note: DECODE in Oracle treats two NULL values as equal (unlike =). If any search value is NULL, use IS NULL in the CASE equivalent:

-- Oracle: DECODE treats NULL = NULL
DECODE(col, NULL, 'empty', col)

-- PostgreSQL
CASE WHEN col IS NULL THEN 'empty' ELSE col END

Migration Actions

1. Stored Procedures

Apply the direct substitutions above. Pay special attention to:

NVL on numeric expressions — COALESCE is type-sensitive in PostgreSQL; ensure both arguments are the same type or cast explicitly.
DECODE with NULL search values — replace with IS NULL guard in the CASE expression.

2. Application Code (inline SQL strings)

Search C# string literals and query builders for NVL(, NVL2(, and DECODE(. Apply the same substitutions.

3. Tests

Write test cases that exercise NULL inputs specifically — the Oracle → PostgreSQL translation of NVL/COALESCE is straightforward, but edge cases around NULL equality in DECODE → CASE are a common source of silent behavioral differences.

2.4 KiB Raw Permalink Blame History