NaN в PostgreSQL: как себя ведёт NULL и арифметика

Условие

Какие утверждения о NaN (Not-a-Number) в PostgreSQL верны?

Решение

NaN ≠ NULL

NaN — это валидное значение в типе numeric или double precision, не NULL. Это «не-число», но СУБД его хранит и оперирует им.

SELECT 'NaN'::numeric;            -- NaN
SELECT 'NaN'::numeric IS NULL;    -- false
SELECT 'NaN'::numeric IS NOT NULL;-- true

Сравнение NaN в PostgreSQL — особенность

В IEEE 754 (стандарте для FLOAT) NaN ≠ NaN. Но PostgreSQL для типа numeric делает другое:

SELECT 'NaN'::numeric = 'NaN'::numeric;   -- true (!)
SELECT 'NaN'::numeric > 1::numeric;        -- true (!)

Это сделано, чтобы NaN можно было использовать в ORDER BY, DISTINCT, индексах. NaN считается «больше всех чисел».

Для double precision правила обычно как в IEEE:

SELECT 'NaN'::double precision = 'NaN'::double precision;  -- false

Арифметика с NaN

SELECT 'NaN'::numeric + 1;     -- NaN
SELECT 'NaN'::numeric * 0;     -- NaN
SELECT 'NaN'::numeric / 1;     -- NaN

Любая операция с NaN даёт NaN.

COALESCE с NaN

SELECT COALESCE('NaN'::numeric, 0);  -- NaN, не 0!

COALESCE подменяет только NULL, а NaN — это значение, поэтому остаётся.

Если нужно заменить NaN на 0:

SELECT CASE WHEN x = 'NaN'::numeric THEN 0 ELSE x END
FROM   t;

NULLIF с двумя NaN

SELECT NULLIF('NaN'::numeric, 'NaN'::numeric);  -- NULL

Поскольку в numeric NaN = NaN истина, NULLIF возвращает NULL. Это специфичная для PG особенность.

Откуда берётся NaN

• Деление 0/0 для double precision.
• Логарифм отрицательного.
• Внешние данные (CSV, API), где «не-число» приходит как «NaN».

В numeric PG также возвращает NaN при некоторых операциях; в Oracle и MySQL может быть NULL или ошибка.

Подводные камни

1. COALESCE(NaN, 0) = NaN — частая ловушка: думали, что замените «плохое значение», а нет.
2. NaN = NaN в numeric — true, в double precision — false. Тип влияет на семантику.
3. SUM колонки с NaN — вернёт NaN, потому что NaN «заражает» агрегаты.
4. AVG с NaN — тоже NaN. Чтобы игнорировать, фильтруйте: WHERE x <> 'NaN'::numeric.
5. CSV-импорт — строки "nan", "NaN", "NAN" могут попасть как валидное NaN-значение.

Эталонный ответ

В PostgreSQL NaN — валидное значение типа numeric, не NULL. Особенности:

• 'NaN'::numeric = 'NaN'::numeric → TRUE (нестандартно для IEEE 754).
• COALESCE(NaN, 0) → NaN (NaN не считается NULL).
• NULLIF(NaN, NaN) → NULL (т.к. равно).
• Любая арифметика с NaN даёт NaN.