COALESCE

Функция для подстановки значений вместо NULL

Теория

После темы NULL появляется вопрос: что делать, если значение не определено, а в результате запроса всё равно нужно показать что-то понятное?

Представьте ситуацию. Мы хотим вывести список пользователей. Если у человека есть телефон, показываем телефон. Если телефона нет, показываем почту. А если и этого нет, выводим фразу 'контакт не указан'. Это и есть типичная задача для COALESCE.

По сути COALESCE — это как CASE на минималках. Данная функция просто проверяет значения по порядку и берёт первое, которое не равно NULL.

Проговорим действие этой функции: попробуй взять первое значение из значений столбцов, перечисленных в скобках внутри функции; если его нет, возьми следующее; если и его нет, возьми ещё одно; если ничего не нашлось, тогда уже вернётся NULL или то запасное значение, которое вы указали в конце.

Важно: COALESCE ничего не меняет в таблице. Она только помогает показать результат запроса в более удобном виде или подставить запасной вариант вместо неопределённого значения.

Синтаксис

COALESCE(value1, value2, value3, ...)

SQL

Внутри COALESCE(...) значения пишутся по порядку.

Логика такая:
• если первое значение есть, функция вернёт его (речь про значения внутри скобок после COALESCE(...));
• если первое значение равно NULL, проверяется второе;
• если второе тоже NULL, проверяется третье;
• и так далее.

То есть COALESCE всегда пытается взять первое доступное значение.

Например, запись COALESCE(city, 'город не указан') читается так: если город указан, показать его; если нет — вывести текст 'город не указан'.

А если написать COALESCE(phone, email, 'контакт не указан'), смысл будет такой: сначала попробуй взять телефон, если его нет — возьми почту, а если нет ни того ни другого — покажи текст 'контакт не указан').

Есть и важное ограничение: значения внутри COALESCE должны быть совместимы по типу данных. Например, нельзя бездумно смешать число, дату и текст в одной функции. В таких случаях SQL обычно выдаст ошибку. Если очень нужно совместить разные типы, сначала их приводят к одному виду через преобразование типа.

И ещё полезно помнить: весь результат COALESCE(...) можно назвать через псевдоним, как обычный вычисляемый столбец. Например: COALESCE(city, 'город не указан') AS city_label.

Примеры

1. Показываем понятный текст вместо неопределенного города

SELECT user_id,
       first_name,
       city,
       COALESCE(city, 'город не указан') AS city_label
FROM users
ORDER BY registration_date DESC;

Если город в строке заполнен, COALESCE вернёт его. Если в city есть NULL, в результате появится текст 'город не указан'.

🔄 Попробуйте изменить запрос:

• Замените текст 'город не указан' на 'нет данных'
• Отсортируйте результат по вычисляемому столбцу: ORDER BY city_label ASC

2. Подставляем запасной статус для заказов без статуса

SELECT order_id,
       order_date,
       status,
       COALESCE(status, 'статус не указан') AS status_label
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;

Идея та же самая, только уже на другой таблице. Если статус заказа ещё не заполнен, вместо пустого значения пользователь увидит понятную фразу.

🔄 Попробуйте изменить запрос:

• Поменяйте текст 'статус не указан' на 'ожидает обработки'
• Оставьте только заказы без статуса, добавив условие AND status IS NULL

3. Ищем товары, где категория не указана

SELECT product_id,
       product_name,
       category,
       price
FROM products
WHERE COALESCE(category, 'без категории') = 'без категории'
ORDER BY price DESC;

В этом примере COALESCE используется не в SELECT, а в WHERE. Если категория у товара не указана, функция временно подставит текст 'без категории', и такую строку можно будет отфильтровать обычным сравнением.

🔄 Попробуйте изменить запрос:

• Замените текст 'без категории' на 'категория не указана' и поправьте сравнение ниже
• Уберите условие WHERE, а в SELECT добавьте COALESCE(category, 'без категории') AS category_label

4. Сортируем заказы так, чтобы пустой статус не мешал

SELECT order_id,
       order_date,
       status
FROM orders
ORDER BY COALESCE(status, 'яяя'),
         order_date DESC;

Этот пример показывает ещё один сценарий применения функции. Если в сортировке участвует поле, где у части строк стоит NULL, можно временно подставить запасное значение и тем самым задать более понятный порядок.

🔄 Попробуйте изменить запрос:

• Подставьте вместо 'яяя' текст '---' и посмотрите, как изменится порядок строк
• Добавьте в SELECT столбец COALESCE(status, 'статус не указан') AS status_label

5. Подставляем текст вместо цены и заранее приводим тип

SELECT product_id,
       product_name,
       COALESCE(price::text, 'цена не указана') AS price_label
FROM products
ORDER BY product_id;

Здесь важен не только сам COALESCE, но и приведение типа. Поле price — числовое, а текст 'цена не указана' — строковый. Если написать COALESCE(price, 'цена не указана'), PostgreSQL выдаст ошибку. Поэтому сначала мы переводим цену в текст через price::text, и только потом подставляем запасной вариант.

🔄 Попробуйте изменить запрос:

• Уберите ::text у поля price и посмотрите, какая ошибка получится

Типичные ошибки

Думают, что COALESCE заменяет и NULL, и пустую строку
'' — это не NULL. Если в колонке лежит пустая строка, COALESCE её не тронет.

Используют COALESCE там, где достаточно простого IS NULL
Если задача просто найти пустые значения, обычно понятнее написать WHERE column IS NULL. COALESCE полезнее там, где нужно именно подставить запасной вариант.

Забывают, что аргументы проверяются слева направо
COALESCE вернёт первое подходящее значение и дальше уже не пойдёт. Поэтому порядок аргументов здесь важен.

Смешивают несовместимые типы данных
Например, запись вроде COALESCE(price, 'нет цены') может закончиться ошибкой, потому что число и текст — это разные типы. Если нужно такое поведение, сначала стоит явно привести значения к одному типу.

Думают, что COALESCE меняет данные в таблице
Нет, не меняет. Он только вычисляет значение в результате запроса. Сами данные в базе остаются как были.

COALESCE

Теория

Синтаксис

Примеры

1. Показываем понятный текст вместо неопределенного города

🔄 Попробуйте изменить запрос:

2. Подставляем запасной статус для заказов без статуса

🔄 Попробуйте изменить запрос:

3. Ищем товары, где категория не указана

🔄 Попробуйте изменить запрос:

4. Сортируем заказы так, чтобы пустой статус не мешал

🔄 Попробуйте изменить запрос:

5. Подставляем текст вместо цены и заранее приводим тип

🔄 Попробуйте изменить запрос:

Типичные ошибки

Практика

Проверь себя