COALESCE(a, b, c) возвращает первый из аргументов, который не равен NULL, поэтому COALESCE(nickname, name) показывает никнейм, если он есть, а иначе имя. NULLIF(a, b) делает обратное: возвращает NULL, когда a равно b. CASE WHEN condition THEN value ... ELSE value END выбирает значение по условию.
У Ben и Chen нет никнейма, поэтому выводятся их полные имена. Ben к тому же получает no phone.
Заменить NULL значением по умолчанию
COALESCE в PostgreSQL заменяет IFNULL, ISNULL и NVL. С его помощью превращайте NULL в 0 перед арифметикой, ведь любая арифметика с NULL даёт NULL, и давайте значение агрегату по нулю строк:
Без COALESCE заказ 2 показывает NULL, а с ним 80, и сумма по пустому набору становится 0 вместо NULL.
Все аргументы должны сводиться к одному типу. COALESCE(quantity, 'none') на целочисленном столбце завершается ошибкой «invalid input syntax for type integer», потому что PostgreSQL пытается прочитать 'none' как целое число. Сначала приведите столбец: COALESCE(quantity::text, 'none'). О приведении типов рассказано на странице о CAST.
Превратить значение в NULL через NULLIF
NULLIF(a, b) возвращает NULL, если a = b, а иначе a. Это защищает от деления на ноль и превращает пустые строки в NULL, чтобы их мог заменить COALESCE:
У spring доля кликов равна 5. summer получает NULL вместо ошибки «division by zero», а его пустой владелец превращается в unassigned.
Выбрать значение через CASE WHEN
Эта форма проверяет условия по порядку и возвращает первое совпадение. Строки, которые ни под что не подошли, получают значение из ELSE или NULL, если ELSE нет:
CASE в ORDER BY отправляет распроданные товары в конец, поэтому mug оказывается последним. Есть и короткая форма, которая сравнивает одно выражение со значениями: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Проверить на NULL она не может, ведь NULL = NULL не истинно. Для этого используйте WHEN x IS NULL в полной форме.
Подсчёт по условию
CASE внутри агрегата считает или суммирует по категориям за один проход. В PostgreSQL есть ещё предложение FILTER, которое выражает то же самое прямее:
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
count(*) FILTER (WHERE status = 'paid') AS paid_too
FROM orders;
Оба варианта считают только оплаченные заказы: count() пропускает NULL, который CASE возвращает для остальных строк.
GREATEST и LEAST игнорируют NULL
GREATEST(a, b, ...) и LEAST(a, b, ...) возвращают наибольший и наименьший аргумент. В PostgreSQL они пропускают аргументы NULL и возвращают NULL, только когда все аргументы равны NULL, поэтому GREATEST(1, NULL, 3) равно 3. MySQL возвращает NULL, если хотя бы один аргумент NULL, и это важно при переносе запросов. Все правила описаны в документации PostgreSQL об условных выражениях.
Часто задаваемые вопросы
Чем в PostgreSQL заменить IFNULL, ISNULL или NVL?
COALESCE(value, fallback). В PostgreSQL нет функций IFNULL (MySQL), ISNULL (SQL Server) или NVL (Oracle); COALESCE входит в стандарт SQL и принимает больше двух аргументов.
Как заменить NULL на 0 в PostgreSQL?
Оберните столбец: COALESCE(discount, 0). Для суммы по пустому набору строк, которая возвращает NULL, пишите COALESCE(SUM(amount), 0).
Вычисляет ли COALESCE все свои аргументы?
Нет. Он вычисляет аргументы по порядку и останавливается на первом, который не NULL, так что следующие аргументы для этой строки не вычисляются.
Можно ли использовать CASE в WHERE?
Да, выражение CASE можно писать везде, где допустимо значение, в том числе в WHERE, ORDER BY и внутри агрегатов. Но в WHERE обычное условие с AND/OR чаще читается проще.