PostgreSQL 18 reference · Conditional and Type Expressions
PostgreSQL NULLIF() Function
Return NULL if two values are equal, otherwise the first one. Commonly used to avoid division by zero.
PostgreSQL 18
Syntax
NULLIF(value1, value2)
Parameters
| Parameter | Description |
|---|---|
value1 |
The value. |
value2 |
The value that should become NULL. |
Return value
NULL or value1.
Examples
SELECT nullif(5, 5) AS same, nullif(5, 3) AS different;
Output
same | different ------+----------- NULL | 5 (1 row)
Run this on your computer — see “Get Started” for setup.
SELECT name, round(100.0 / nullif(stock, 0), 2) AS pct_per_unit
FROM products
WHERE product_id IN (2, 3);
Output
name | pct_per_unit --------------------------+-------------- Clean Code | 6.67 The Pragmatic Programmer | NULL (2 rows)
Run this on your computer — see “Get Started” for setup.
In other languages
NULLIF()· SQLite
Related
COALESCE()· Return the first argument that isn't NULL. The standard way to replace NULL with a default.CASE· Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END, usable anywhere an expression is.- SQL NULL Values · tutorial