PostgreSQL – NULLIF Statement

The NULLIF function is used to compare two expressions and return NULL if they are equal, otherwise it returns the first expression. This function is often used to handle situations where you want to avoid having a particular value and replace it with NULL based on a condition.

The syntax of the NULLIF function is as follows:

NULLIF(expression1, expression2)

Here’s an example of how the NULLIF function can be used:

SELECT NULLIF(column1, column2) AS result FROM table_name;

Example: Suppose you have a table called inventory with columns total_quantity and reserved_quantity. You want to calculate the available quantity by subtracting the reserved quantity from the total quantity. However, if the total quantity and reserved quantity are the same, you want to show NULL instead of 0 available quantity:

SELECT total_quantity, reserved_quantity, NULLIF(total_quantity - reserved_quantity, 0) AS available_quantity FROM inventory;

In this example, if the subtraction result would be 0, the NULLIF function will return NULL instead. This can be useful to avoid displaying misleading information or distinguishing between situations where no quantity is available and situations where there is simply no change in quantity.

The NULLIF function helps you handle scenarios where you want to suppress certain values and replace them with NULL based on specific conditions, making your query results more accurate and meaningful.