---
title: "NVL in Postgres: NVL vs COALESCE and the Porting Errors"
description: "PostgreSQL has no NVL function. The Postgres NVL equivalent is COALESCE(value, default), which is SQL-standard, takes any number of arguments and returns the…"
canonical: "https://www.aifordatabase.com/blog/nvl-in-postgres/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-13
updated: 2026-11-13
---

# NVL in Postgres: NVL vs COALESCE and the Porting Errors

**TL;DR:** PostgreSQL has no NVL function. The Postgres NVL equivalent is `COALESCE(value, default)`, which is SQL-standard, takes any number of arguments and returns the first one that is not null. Most Oracle code ports by renaming NVL to COALESCE; the three things that break are type mismatches, empty strings and NVL2.

There is no NVL in Postgres: run `SELECT nvl(phone, 'n/a') FROM customers` and PostgreSQL 18 answers `ERROR: function nvl(text, unknown) does not exist`. The replacement is `COALESCE(phone, 'n/a')`. The [PostgreSQL manual](https://www.postgresql.org/docs/current/functions-conditional.html) describes COALESCE as the SQL-standard function that "provides capabilities similar to NVL and IFNULL, which are used in some other database systems." For two arguments the result is the same as Oracle's NVL in almost every case. The differences sit in the details: COALESCE accepts more than two arguments, it is strict about data types where Oracle converts silently, and Postgres treats `''` as a real value while Oracle treats it as null. Every error message below was reproduced on PostgreSQL 18.6 in November 2026.

## NVL vs COALESCE at a glance

| Criterion | Oracle NVL | PostgreSQL COALESCE |
| --- | --- | --- |
| Exists in PostgreSQL | No (only via the orafce extension) | Yes, built in |
| Arguments | Exactly 2 | 1 or more |
| Standard | Oracle-specific | SQL standard (also in Oracle, MySQL, SQL Server) |
| Mixed types, e.g. number and text | Implicitly converts expr2 to expr1's type | Fails unless types share a category |
| Return type | Type of expr1 (VARCHAR2 for character data) | Common type of all arguments |
| Empty string `''` | Treated as null, so the default is returned | A real value, returned as is |
| Evaluation of later arguments | Not promised by the NVL docs | Skipped once a non-null value is found |
| Two-way variant | NVL2(expr, if\_not\_null, if\_null) | None; use CASE |

Sources: [Oracle NVL](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/NVL.html), [Oracle COALESCE](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/COALESCE.html), [PostgreSQL conditional expressions](https://www.postgresql.org/docs/current/functions-conditional.html); checked against Oracle Database 23ai and PostgreSQL 18 docs, November 2026.

## Does PostgreSQL have NVL?

No. PostgreSQL ships no function called NVL, and calling one fails:

```
SELECT nvl(phone, 'n/a') FROM customers;
-- ERROR:  function nvl(text, unknown) does not exist
-- HINT:  No function matches the given name and argument types.
--        You might need to add explicit type casts.
```

The hint about type casts is misleading here: no cast will help because the function does not exist at all. The fix is to rewrite the call with COALESCE:

```
SELECT coalesce(phone, 'n/a') AS phone FROM customers;
```

`COALESCE(value [, ...])` returns the first of its arguments that is not null, and returns null only if every argument is null. It works in any clause where an expression is allowed: SELECT, WHERE, JOIN conditions, GROUP BY, ORDER BY and HAVING.

## What is the difference between NVL and COALESCE?

NVL is a two-argument Oracle function; COALESCE is the SQL-standard version that takes a list. Oracle's own documentation calls COALESCE "a generalization of the NVL function." In practice they differ on four points.

**1\. Argument count.** NVL takes exactly two expressions. COALESCE takes as many as you need, so a nested Oracle `NVL(a, NVL(b, c))` collapses to one call:

```
-- Oracle
SELECT NVL(mobile_phone, NVL(work_phone, 'no phone')) FROM contacts;

-- PostgreSQL
SELECT coalesce(mobile_phone, work_phone, 'no phone') FROM contacts;
```

**2\. Type rules.** Oracle NVL converts the second argument to the type of the first: "If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1." PostgreSQL resolves all COALESCE arguments to one common type using the same algorithm as UNION and CASE ([Section 10.5](https://www.postgresql.org/docs/current/typeconv-union-case.html)), and fails if the types are in different categories. This is where most porting errors come from (next section).

**3\. Empty strings.** Oracle "treats a character value with a length of zero as null" ([Oracle Nulls](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Nulls.html)). PostgreSQL does not. Same data, different output:

```
-- nickname = '' (empty string) for Ana, NULL for Ben
SELECT name, coalesce(nickname, '(none)') AS nickname FROM customers;
-- PostgreSQL: Ana -> ''        Ben -> '(none)'
-- Oracle NVL: Ana -> '(none)'  Ben -> '(none)'
```

**4\. Evaluation.** PostgreSQL documents that COALESCE "only evaluates the arguments that are needed to determine the result", and Oracle documents short-circuit evaluation for its COALESCE. The Oracle NVL page makes no such promise, so if your Oracle code relied on NVL's behaviour with an expensive or side-effecting second argument, test it after the port.

## Why does COALESCE fail with a type mismatch when porting from Oracle?

COALESCE fails because PostgreSQL will not silently turn a number or date into text the way Oracle NVL does. Oracle code such as `NVL(age, 'N/A')` often survives in Oracle because of implicit conversion; in PostgreSQL the same call breaks in one of two ways, depending on whether the default is a literal or a column.

A quoted literal has type `unknown`, so Postgres tries to read it as the column's type:

```
SELECT coalesce(age, 'N/A') FROM customers;          -- age is integer
-- ERROR:  invalid input syntax for type integer: "N/A"

SELECT coalesce(created_at, 'never') FROM customers; -- created_at is date
-- ERROR:  invalid input syntax for type date: "never"

SELECT coalesce(age, '') FROM customers;
-- ERROR:  invalid input syntax for type integer: ""
```

Two typed expressions from different categories fail at type resolution:

```
SELECT coalesce(age, name) FROM customers;  -- integer and text
-- ERROR:  COALESCE types integer and text cannot be matched
```

The fix is the one Oracle's own NVL example uses (`NVL(TO_CHAR(commission_pct), 'Not Applicable')`): convert explicitly so both arguments are text.

```
SELECT coalesce(age::text, 'N/A')                       AS age,
       coalesce(to_char(created_at, 'YYYY-MM-DD'), 'never') AS created
FROM customers;
```

Numbers of different numeric types are fine: `coalesce(age, 1.5)` on an integer column returns `numeric`, because both arguments are in the numeric category and numeric is the preferred type. Our [PostgreSQL CAST guide](https://www.aifordatabase.com/blog/postgresql-cast/) covers the `::` syntax and which conversions are implicit.

## Can I create an NVL function in Postgres?

Yes. If you have hundreds of NVL calls and want to port them without a rewrite, a SQL function that wraps COALESCE works. Use the `anycompatible` pseudo-type (PostgreSQL 13 and later, per the [13.0 release notes](https://www.postgresql.org/docs/release/13.0/)), not `anyelement`:

```
CREATE OR REPLACE FUNCTION nvl(anycompatible, anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$ SELECT coalesce($1, $2) $$;

SELECT nvl(phone, 'n/a'), nvl(discount, 0) FROM customers;  -- works
```

Why not `anyelement`? It demands that both arguments have exactly the same type, which breaks the most common call shape:

```
-- with nvl(anyelement, anyelement)
SELECT nvl(discount, 0) FROM customers;  -- discount is numeric
-- ERROR:  function nvl(numeric, integer) does not exist
SELECT nvl(NULL, 'x');
-- ERROR:  could not determine polymorphic type because input has type unknown
```

Keep the wrapper in `LANGUAGE sql`. PostgreSQL inlines a simple SQL function, so `EXPLAIN VERBOSE SELECT nvl(age, 0) FROM customers` shows `Output: COALESCE(age, 0)`, the same plan as writing COALESCE by hand, and the second argument is still skipped for non-null rows. A PL/pgSQL wrapper is not inlined: its arguments are evaluated before the call, so `nvl_plpgsql(age, expensive_or_failing_expr)` runs the second expression on every row. In our test a runtime division by zero that COALESCE and the SQL wrapper skipped made the PL/pgSQL version fail.

The wrapper does not change Postgres's type rules: `nvl(age, 'N/A')` still fails with `invalid input syntax for type integer`, and it does not treat `''` as null. Use it as a migration bridge, then replace calls with COALESCE as you touch the code.

### What about the orafce extension?

[orafce](https://github.com/orafce/orafce) is an open-source extension that reimplements Oracle functions in PostgreSQL, including `nvl`, `nvl2`, `decode` and `lnnvl`. Its README notes support on AWS Aurora PostgreSQL and Azure Database for PostgreSQL. After `CREATE EXTENSION orafce;` the functions live in the `oracle` schema, so either call `oracle.nvl(...)` or put the schema first:

```
SET search_path TO oracle, "$user", public, pg_catalog;
SELECT nvl(phone, 'n/a'), nvl2(phone, 'has phone', 'no phone') FROM customers;
```

orafce's generic `oracle.nvl(anyelement, anyelement)` is a C function, with extra SQL overloads for common integer, bigint, numeric and double precision pairs. orafce also ships `oracle.replace_empty_strings()` triggers for teams that want to keep only NULLs in text columns, which is the closest you get to Oracle's empty-string behaviour.

## How do you replace NVL2, IFNULL and ISNULL in Postgres?

All of them map to COALESCE or CASE. NVL2 returns one value when the first argument is not null and another when it is; Postgres has no NVL2 (`function nvl2(text, unknown, unknown) does not exist`), so write a CASE. Our [PostgreSQL CASE WHEN guide](https://www.aifordatabase.com/blog/postgresql-case-when/) covers the full syntax.

| Source function | Database | PostgreSQL replacement |
| --- | --- | --- |
| `NVL(a, b)` | Oracle | `coalesce(a, b)` |
| `NVL(a, b)` on text where `''` should count as null | Oracle | `coalesce(nullif(a, ''), b)` |
| `NVL2(a, x, y)` | Oracle | `CASE WHEN a IS NOT NULL THEN x ELSE y END` |
| `IFNULL(a, b)` | MySQL, SQLite | `coalesce(a, b)` |
| `ISNULL(a, b)` | SQL Server | `coalesce(a, b)` |
| `NULLIF(a, b)` | Oracle, SQL Server, MySQL | `nullif(a, b)` (same) |

Porting date logic from the same codebase? See [GETDATE in PostgreSQL](https://www.aifordatabase.com/blog/postgresql-getdate/) and [DATEDIFF in PostgreSQL](https://www.aifordatabase.com/blog/postgresql-datediff/).

## When should you choose COALESCE and when a wrapper?

Choose plain COALESCE if:

*   You are writing new code or touching a query anyway. It is standard SQL, runs unchanged on Oracle, MySQL and SQL Server, and every Postgres developer reads it.
*   You need a fallback chain of three or more values.

Choose an `anycompatible` SQL wrapper or orafce if:

*   You are mid-migration with a large Oracle codebase and want queries to run before they are rewritten.
*   You also need other Oracle functions (orafce covers `decode`, `nvl2`, `add_months` and more).

Either way, search the ported code for NVL calls on text columns and on mixed types; those are the two places where results or errors differ.

VIDEO · [PostgreSQL: Coalesce | Course | 2019](https://www.youtube.com/watch?v=3BnJUs5eAqc) (Amigoscode)

## Where COALESCE shows up after the migration

Once the schema lives in PostgreSQL, the same null handling decides what reports show: a customer with no plan becomes "free", a missing discount becomes 0, a blank region becomes "unknown". Getting those defaults right in every dashboard query is tedious. AI for Database connects to PostgreSQL and Oracle side by side, answers questions like "revenue by region last month, unknown regions grouped together" in plain English, and shows the generated SQL (COALESCE included) so you can check how nulls were handled before trusting a number. Try AI for Database free at aifordatabase.com. More on what it supports for Postgres is on our [PostgreSQL page](https://www.aifordatabase.com/databases/postgresql/).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### Does PostgreSQL have NVL?

No. PostgreSQL has no built-in NVL function; calling it returns function nvl(...) does not exist. Use COALESCE(value, default), or install the orafce extension, which adds oracle.nvl.

### What is the Postgres NVL equivalent?

COALESCE is the Postgres NVL equivalent. NVL(a, b) becomes coalesce(a, b). If the Oracle code relied on empty strings counting as null, use coalesce(nullif(a, ''), b).

### Is COALESCE slower than NVL?

Not in PostgreSQL in any way you can measure from the function itself: COALESCE is built into the executor, and a LANGUAGE sql NVL wrapper is inlined into the same COALESCE expression, as EXPLAIN VERBOSE shows. COALESCE also skips arguments after the first non-null one, so an expensive fallback runs only when needed.

### Why do I get "COALESCE types integer and text cannot be matched"?

The arguments belong to different type categories and PostgreSQL will not convert between them implicitly. Cast one side so they match, for example coalesce(age::text, name).

### Does COALESCE treat an empty string as NULL in Postgres?

No. In PostgreSQL '' is a non-null value, so coalesce('', 'x') returns ''. Oracle treats zero-length strings as null, which is why NVL behaves differently on the same data. Wrap the column in nullif(col, '') to get Oracle's result.

### Can COALESCE take more than two arguments?

Yes. coalesce(mobile\_phone, work\_phone, home\_phone, 'no phone') returns the first non-null value in order, which replaces nested NVL calls.

Read next

*   [DEFAULT PORT FOR SQL SERVER: 1433 AND EVERY OTHER PORT →](https://www.aifordatabase.com/blog/default-port-for-sql-server/)
*   [SQL SERVER ALERT FOR LONG RUNNING QUERIES: FULL SETUP →](https://www.aifordatabase.com/blog/sql-server-alert-long-running-queries/)
*   [PACING DASHBOARD: 8 EXAMPLES WITH FORMULAS AND SQL →](https://www.aifordatabase.com/blog/pacing-dashboard-examples/)

Source: https://www.aifordatabase.com/blog/nvl-in-postgres/