NULLIF function behavior in PostgreSQL

Introduction

NULLIF helps you avoid errors by returning NULL when two values are the same, making your data cleaner and easier to work with.

When you want to replace a value with NULL if it matches another value.

When you want to avoid division by zero errors by returning NULL instead of zero.

When you want to mark certain values as missing or invalid by turning them into NULL.

When comparing two columns and you want NULL instead of repeated values.

When cleaning data to handle special cases without complex conditions.

Syntax

PostgreSQL

NULLIF(value1, value2)

Returns NULL if value1 equals value2; otherwise, returns value1.

Both values should be of compatible types.

Examples

Returns NULL because both values are equal.

PostgreSQL

SELECT NULLIF(5, 5);

Returns 5 because the values are different.

PostgreSQL

SELECT NULLIF(5, 3);

Returns NULL because the strings are equal.

PostgreSQL

SELECT NULLIF('apple', 'apple');

Returns 'apple' because the strings are different.

PostgreSQL

SELECT NULLIF('apple', 'orange');

Sample Program

This query returns product IDs and prices. If the price is 0, it returns NULL instead to avoid zero values.

PostgreSQL

SELECT product_id, price, NULLIF(price, 0) AS price_or_null FROM products ORDER BY product_id;

Important Notes

NULLIF is useful to prevent errors like division by zero by turning zero into NULL.

If value1 is NULL, NULLIF returns NULL regardless of value2.

NULLIF only compares two values; it does not handle multiple comparisons.

Summary

NULLIF returns NULL when two values are equal, otherwise returns the first value.

It helps handle special cases like zero values or repeated data.

Use NULLIF to simplify queries and avoid errors.