Menu

COALESCE, NULLIF и CASE WHEN в PostgreSQL

COALESCE(a, b, ...) возвращает первый аргумент, который не NULL, NULLIF(a, b) превращает значение в NULL, а CASE WHEN выбирает значение по условию.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

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 чаще читается проще.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать