How to NVL in PostgreSQL

How do I replace Oracle NVL for NULL handling in PostgreSQL?

Emulate Oracle’s NVL function by using COALESCE (or a tiny wrapper) to substitute default values when a column is NULL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does Oracle’s NVL do?

NVL(expr1, expr2) returns expr1 unless it is NULL, in which case it returns expr2. PostgreSQL lacks NVL but offers the ANSI-standard COALESCE that behaves the same when given two arguments.

How do I replace NVL in PostgreSQL?

Use COALESCE(column, default).It inspects arguments left to right and returns the first non-NULL value, matching NVL when only two arguments are supplied.

Example: default email

SELECT COALESCE(email,'unknown@example.com') AS email FROM Customers; returns a fallback address whenever email is NULL.

Can I add more than two options?

Yes.COALESCE supports any number of arguments, letting you chain fallbacks such as column, derived value, then literal default.

Should I create a custom NVL wrapper?

Teams migrating from Oracle often create CREATE FUNCTION nvl(a anyelement,b anyelement) that simply calls COALESCE for smoother porting. Use it sparingly to avoid hiding PostgreSQL idioms from new developers.

Best practices for NULL handling

1) Always cast literals to the target type to avoid ambiguous-type errors. 2) Keep data types consistent across all COALESCE arguments.3) Prefer IS DISTINCT FROM for NULL-safe comparisons.

Common errors and fixes

ERROR: function nvl(text, unknown) does not exist → replace with COALESCE or create a typed NVL wrapper. ERROR: could not determine data type → cast each literal, e.g., '0'::numeric.

Why How to NVL in PostgreSQL is important

How to NVL in PostgreSQL Example Usage


-- Show customer, latest order total, or 0 when no orders exist
SELECT c.name,
       COALESCE(o.total_amount, 0) AS last_order_amount
FROM Customers c
LEFT JOIN LATERAL (
    SELECT total_amount
    FROM Orders o
    WHERE o.customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) o ON TRUE;

How to NVL in PostgreSQL Syntax


-- Oracle style
-- NVL(expr1, expr2)

-- PostgreSQL equivalent
COALESCE ( expr1 [, expr2 [, ...] ] )

-- Ecommerce example 1: substitute unknown email
SELECT COALESCE(email, 'unknown@example.com') AS email
FROM Customers;

-- Ecommerce example 2: choose first available price
SELECT COALESCE(discount_price, price) AS final_price
FROM Products;

-- Create a lightweight NVL wrapper for migration
CREATE OR REPLACE FUNCTION nvl(a ANYELEMENT, b ANYELEMENT)
RETURNS ANYELEMENT AS $$
    SELECT COALESCE($1, $2);
$$ LANGUAGE SQL IMMUTABLE;

Common Mistakes

Using NVL directly: PostgreSQL raises "function nvl does not exist". Fix by replacing NVL with COALESCE or defining a wrapper nvl() that calls COALESCE.
Mixing different data types in COALESCE arguments causes implicit cast failures. Ensure all arguments share a compatible type or cast explicitly.