The expression toolkit: NULLIF, GREATEST, row comparisons, ANY and VALUES

NULLIF: avoid division by zero

A ratio per customer, shipped orders per pending order:

SELECT customer_id,
       count(*) FILTER (WHERE status = 'shipped') / count(*) FILTER (WHERE status = 'pending')
FROM orders GROUP BY customer_id;

ERROR:  division by zero

Most customers have no pending orders, and one zero stops the whole query. NULLIF(x, 0) returns NULL when x is 0, and dividing by NULL gives NULL instead of an error:

SELECT customer_id,
       count(*) FILTER (WHERE status = 'shipped')::numeric
       / NULLIF(count(*) FILTER (WHERE status = 'pending'), 0) AS shipped_per_pending
FROM orders GROUP BY customer_id ORDER BY customer_id;

Customers without pending orders now show an empty ratio. It is honestly “undefined”, rather than a fake 0 or a crash.

GREATEST and LEAST: clamp a value

A 10% sale, but nothing may sell below 1,000:

SELECT name, price, GREATEST(price * 0.9, 1000) AS sale_price
FROM products ORDER BY price LIMIT 4;

      name       |  price  | sale_price
-----------------+---------+------------
 Cable organiser |  499.00 |       1000
 Wireless mouse  | 1200.00 |   1080.000
 Laptop stand    | 1899.00 |   1709.100
 Desk lamp       | 2199.00 |   1979.100

(The cable organiser would now cost more on sale than before: real code would use LEAST(price, GREATEST(price * 0.9, 1000)).)

Their NULL rule differs from almost everything else in SQL: they ignore NULLs.

SELECT GREATEST(1, NULL, 3), 1 + NULL;   -- 3, and NULL

The SQL standard says GREATEST should return NULL if any argument is NULL, and some other databases do. PostgreSQL does not, so code moved between databases can behave differently here.

Row comparisons: compare several columns in order

(a, b) < (x, y) compares like a dictionary: first a with x, and only if they are equal, b with y.

SELECT (2025, 3) < (2025, 7)              AS same_year_earlier_month,   -- true
       ('Pune', 'Asha') < ('Pune', 'Divya') AS same_city_by_name,        -- true
       (1, NULL) < (2, 0)                   AS decided_by_first,          -- true
       (1, NULL) < (1, 0)                   AS reaches_the_null;          -- NULL

The comparison stops at the first pair that differs. If that pair contains a NULL, the answer is unknown. Row comparisons are the key to fast pagination, in Pagination and large result sets.

ANY and ALL: compare with many values

= ANY tests against every element of an array or every row of a subquery:

SELECT name FROM products WHERE category = ANY (ARRAY['Audio', 'Video']);

That is how application code should pass “one of these values” as a single array parameter. > ALL means “greater than every one”: products dearer than every accessory:

SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Accessories')
ORDER BY price;

Four products, from the 7,999 SSD up to the 18,999 monitor.

VALUES: a small table, right there

VALUES builds a constant table inside a query. Mapping status codes to labels for display, without creating a table:

SELECT o.id, o.status, s.label
FROM orders o
JOIN (VALUES ('pending', 'Awaiting payment'), ('paid', 'Preparing'),
             ('shipped', 'On its way'), ('cancelled', 'Cancelled')) AS s(status, label)
  ON s.status = o.status
WHERE o.customer_id = 1
ORDER BY o.id;

 id  | status  |   label
-----+---------+------------
 101 | shipped | On its way
 103 | shipped | On its way
 108 | paid    | Preparing

Check yourself

1. How does NULLIF prevent a division-by-zero error?

   Show answer

   NULLIF(x, 0) returns NULL when x is 0. Dividing by NULL gives NULL rather than an error.

   I could explain this without looking

2. What does GREATEST(5, NULL) return in PostgreSQL, and why does that matter?

   Show answer

   5, because PostgreSQL’s GREATEST ignores NULLs. The SQL standard, and some other databases, would return NULL, so the same query can behave differently elsewhere.

   I could explain this without looking

3. Is (2025, 12) < (2026, 1) true? Why?

   Show answer

   Yes. Row comparison compares the first elements first; 2025 is less than 2026, so the second elements are never compared.

   I could explain this without looking