ORACLE provides a built-in function named NVL() that replaces the NULL entries with a default entry/value. When it comes to PostgreSQL, it doesn’t support the NVL() function. However, it offers a COALESCE() function that can be used as an alternative to NVL() function.

This blog post will explain the following topics/concepts regarding the NVL() function:

• What is NVL() and How Does it Work?
• What is the Postgres Equivalent of NVL() Function?
• Is ORACLE’s NVL() Function the Same as the Postgres COALESCE()?
• Practical Demonstration

So, let’s begin!

What is NVL() and How Does it Work?

NVL() is an inbuilt function in ORACLE that replaces the NULL values with some other meaningful values in the resultant table of a query. Following will be the syntax for the NVL() function:

NVL(val_1, val_2);

It accepts two values as arguments:

- If the value of the first parameter is non-null, then the NVL() function will retrieve val_1.
- If the value of the first parameter “val_1” is equal to “NULL”, then the NVL() function will retrieve the value of the second parameter “val_2”.
- So, in the second parameter, users can specify any value as an alternative to the “NULL” value. So that if the value of the first argument becomes “NULL”, the NVL() function retrieves the specified value instead of retrieving the null value.

What is the Postgres Equivalent of NVL() Function?

In PostgreSQL, the COALESCE() function is used as an alternative to the NVL() function. It takes “n” arguments and retrieves the first non-null value among them.

COALESCE (value_1, value_2, …, value_n);

The COALESCE() function stops its execution immediately when it finds a first non-null value.

When working with the table’s data, you can specify the column name as the first argument and any non-null value as the second argument. Consequently, if a null value occurs in a table, then it will be replaced with the specified non-null value:

COALESCE(col_name, 0);

In the above syntax, we specified “0” as a second argument; however, you can specify any non-null value of your choice.

Is ORACLE’s NVL() Function the Same as the Postgres COALESCE()?

Between these two functions, there are a couple of notable differences. The below-listed points will help you understand this concept in a better way:

- ORACLE's NVL() function takes only 2 arguments; on the other hand, Postgres’ COALESCE() function can accept various arguments.
- The NVL() function evaluates both arguments and retrieves the result accordingly.
- Postgres’ COALESCE() function stops the execution when it finds the first non-null entry.

So, all in all, both NVL() and COALESCE() functions can be used for the same purpose, i.e., replacing a NULL value with any value other than NULL.

Practical Demonstration

The “products_info” table has already been created in the “example” database. To fetch the table’s data, execute the SELECT query as follows:

SELECT * FROM products_info;

In the above snippet, you can see that the second record in the pro_discount column has a null value. Let’s implement the COALESCE() function on the “pro_price” and “pro_discount” columns to find the discounted price of each product:

SELECT pro_price - COALESCE(pro_discount, 0) AS discounted_price
FROM products_info;

In the above snippet:

- The COALESCE() function is used to find the products’ discounted prices.
- The two arguments are passed to the COALESCE() function: the “pro_discount” column and “0” to replace the null values with 0.
- The result of the COALESCE() function will be subtracted from the product’s original price.
- The discounted price for each product will be displayed in the "discounted_price" column.

The above snippet shows that the COALESCE() function successfully retrieves each product's discounted price.

Click here to learn more about the Postgres COALESCE() function.

Conclusion

In ORACLE, the NVL() function replaces a null value with any user-specified value other than null. However, Postgres doesn’t support the NVL() function. In Postgres, a built-in function named COALESCE() is used as an alternative to the NVL() function. To do so, specify the expression/table’s column as the first argument and any non-null value as the second argument. Consequently, the null values will be replaced with the specified non-null value. This write-up presented an in-depth overview of is NVL() function same as COALESCE() in PostgreSQL.