Зачем нужны функции работы с NULL

В прошлом уроке мы выяснили, что NULL — это не значение, а маркер «неизвестно», и что обычные операторы с ним работают через трёхзначную логику. На практике этого мало. Нам нужны инструменты, чтобы подменять NULL осмысленным значением перед арифметикой, выводом, индексами и сравнениями. И наоборот — иногда нам нужно превратить значение в NULL, чтобы убрать его из агрегатов.

Этим занимаются три функции: COALESCE, NULLIF и DDL-конструкция DEFAULT.

COALESCE — «возьми первое не-NULL»

COALESCE(a, b, c, ...) возвращает первое из аргументов, которое не NULL. Если все NULL — возвращает NULL.

Заметь: чтобы COALESCE сработал, все аргументы должны быть совместимых типов. birth_year — INT, 'unknown' — TEXT, поэтому пришлось привести через ::text.

Типичные сценарии COALESCE

Фолбэк в иерархии настроек:

SELECT COALESCE(user_setting, team_setting, org_setting, 'default') AS effective;

Замена NULL на ноль перед арифметикой:

SELECT id, COALESCE(discount_cents, 0) + base_price_cents AS total FROM ...

Это критично: base_price + NULL = NULL. Любая арифметика с NULL даёт NULL. Если ты не подстраховался COALESCE, одна строка с пропущенной скидкой обнулит весь столбец.

Вторая строка в naive — NULL. В safe — 1500. На этом ломаются отчёты.

NULLIF — обратная операция

NULLIF(a, b) возвращает NULL, если a = b, иначе возвращает a. Это «превращалка в NULL по условию».

Зачем такое нужно? Самый частый случай — избегание деления на ноль:

Когда den = 0, NULLIF(den, 0) возвращает NULL. Деление на NULL даёт NULL — а не ошибку. Если бы мы оставили den как есть, второй ряд бросил бы division by zero.

Второй сценарий — очистка «магических значений». В унаследованных системах часто пропуски маркируются строкой 'N/A', '-', '' или числом -1. NULLIF(column, '-1') превращает их в честные NULL, чтобы дальше работала вся остальная NULL-инфраструктура (IS NULL, COUNT(col) и так далее).

DEFAULT — декларативная замена в DDL

DEFAULT — это не функция, а свойство колонки в CREATE TABLE. Если при INSERT значение для этой колонки не указано или указано как DEFAULT, СУБД подставит дефолтное значение.

CREATE TABLE events (
  id          SERIAL PRIMARY KEY,
  payload     JSONB,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  retries     INT         NOT NULL DEFAULT 0,
  status      TEXT        NOT NULL DEFAULT 'pending'
);

Тут три DEFAULT-значения с разными ролями:

• occurred_at DEFAULT now() — вычисляемое выражение, переоценивается на каждой вставке.
• retries DEFAULT 0 — литерал, нулевой счётчик.
• status DEFAULT 'pending' — литерал, бизнес-значение по умолчанию.

DEFAULT работает только если ты не указал колонку в INSERT (или указал DEFAULT явно). Если ты вставляешь NULL, DEFAULT не сработает — NULL пройдёт как есть (если, конечно, нет NOT NULL).

NULL в агрегатах: главная неочевидность

Большинство агрегатных функций — SUM, AVG, MAX, MIN, COUNT(col) — игнорируют NULL. Они работают так, будто строк с NULL в этой колонке просто не существует.

Исключение — COUNT(*). Он считает строки, а не значения, и NULL-ы попадают в счётчик наравне со всеми остальными.

COUNT(*) - COUNT(col) — это стандартная идиома для подсчёта NULL без фильтра.

Ловушка: SUM по пустому множеству

SUM игнорирует NULL — но если в группе вообще нет ни одной не-NULL строки, результат не 0, а NULL. Тот же эффект — у MAX, MIN, AVG.

В обоих запросах результат — NULL, не 0. Если дальше идёт арифметика — она тоже отравится NULL. Защита — COALESCE(SUM(x), 0) всегда, когда нужна гарантия числа.

SELECT customer_id, COALESCE(SUM(total_cents), 0) AS lifetime_value
FROM orders
GROUP BY customer_id;

DISTINCT и FILTER — современные альтернативы

С Postgres 9.4+ есть удобная конструкция FILTER (WHERE ...) — она позволяет считать подмножество внутри агрегата без подзапросов:

FILTER элегантнее, чем CASE WHEN ... THEN 1 END внутри COUNT, и работает с любым агрегатом.

Чек-лист

• COALESCE(a, b, c, ...) — первое не-NULL значение. Идеален как фолбэк.
• NULLIF(a, b) — превращает a в NULL, если a = b. Главный сценарий — деление на ноль.
• DEFAULT в DDL подставляется при пропуске колонки в INSERT, но не заменяет явно вставленный NULL.
• Агрегаты SUM, AVG, MAX, MIN, COUNT(col) игнорируют NULL. COUNT(*) — нет.
• COUNT(*) - COUNT(col) — стандартный приём подсчёта NULL в колонке.
• SUM/AVG по полностью пустой или полностью NULL группе возвращают NULL, не 0. Оборачивай в COALESCE для гарантии числа.
• FILTER (WHERE ...) — современный способ считать срезы внутри агрегата.