NULLIF
Compares two values and returns NULL if they are equal.
PostgreSQL 17.5
NULLIF(value_1, value_2)Parameters
- value_1
- Value returned if the arguments are not equal
- value_2
- Value to compare with value_1
Return value
NULL if the arguments are equal, otherwise value_1
Examples
Equal values give NULL
PostgreSQL 17.5
SELECT NULLIF('SQL Academy', 'SQL Academy');Protection against division by zero
PostgreSQL 17.5
SELECT 100 / NULLIF(0, 0) AS result;Empty promo codes are not counted
PostgreSQL 17.5
SELECT COUNT(promo_code) AS not_null,
COUNT(NULLIF(promo_code, '')) AS filled
FROM orders;Pages per minute without division by zero
PostgreSQL 17.5
SELECT session_id,
pages_viewed,
duration_sec,
ROUND(pages_viewed * 60.0 / NULLIF(duration_sec, 0), 1) AS pages_per_minute
FROM sessions
ORDER BY duration_sec
LIMIT 5;Details
Two common uses: protection against division by zero — x / NULLIF(y, 0) returns NULL instead of an error, and turning empty strings into NULL — NULLIF(column, ''), so that COUNT skips them and COALESCE replaces them.