Сравнение с NULL¶
Любое сравнение произвольного значения с NULL возвращает NULL (неопределенное значение):
Примеры:
denis=# select coalesce((1 <> null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((1 = null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((1 < null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((1 > null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((null = null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((null <> null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
Поэтому выражения/переменные, которые могут принимать неопределенные значения, надо осторожно использовать в сравнениях. Если в выражении попадётся неопределенное значение, то результат всего выражения может быть очень неожиданным.
Использование неопределенного значения в сложном сравнении с использование булевых операторов AND, OR, NOT тоже не несет в себе какого-нибудь позитива - хотя и результаты логических операция не противоречат болевой логики, но их сложно анализировать разработчикам.
denis=# select coalesce((null or true)::varchar, '<null>');
coalesce
----------
true
(1 row)
denis=# select coalesce((null and true)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((null and false)::varchar, '<null>');
coalesce
----------
false
(1 row)
denis=# select coalesce((null or false)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
denis=# select coalesce((not null)::varchar, '<null>');
coalesce
----------
<null>
(1 row)
Для сравнения на равенство двух значение, которые могут принимать неопределенные значения, можно использовать конструкцию IS DISTINCT FROM, которая возвращает True, если значения отличаются друг от друга:
denis=# select coalesce((null is distinct from null)::varchar, '<null>');
coalesce
----------
false
(1 row)
denis=# select coalesce((null is distinct from 1)::varchar, '<null>');
coalesce
----------
true
(1 row)
denis=# select coalesce((1 is distinct from 1)::varchar, '<null>');
coalesce
----------
false
(1 row)
Предупреждение
При использовании операторов is distinct from и is not distinct from в запросе индексы задействованы не будут.
denis=# explain select * from customers where id is not distinct from 222;
QUERY PLAN
------------------------------------------------------------
Seq Scan on customers (cost=0.00..803.00 rows=1 width=17)
Filter: (NOT (id IS DISTINCT FROM 222))
(2 rows)
denis=# explain select * from customers where id = 222;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using customers_pkey on customers (cost=0.00..8.28 rows=1 width=17)
Index Cond: (id = 222)
(2 rows)
Это частично решает проблему сравнения неопределенных значений. Однако осталась проблема сравнений на больше-меньше. Это можно решить через функцию COALESCE:
denis=# select coalesce(1 < null, false);
coalesce
----------
f
(1 row)
denis=# select coalesce(2 > null, true);
coalesce
----------
t
(1 row)
При разработке разработке хранимых процедур на pl/pgsql вышеописанная проблема так же сохраняется, но не так остро. Хотя остаётся не менее коварной:
DECLARE
_str text;
_int int4;
BEGIN
_str := NULL;
_int := NULL;
IF _str <> '' THEN
RAISE NOTICE 'str: True';
ELSE
RAISE NOTICE 'str: False';
END IF;
IF _int > 0 THEN
RAISE NOTICE 'int: True';
ELSE
RAISE NOTICE 'int: False';
END IF;
IF NULL THEN
RAISE NOTICE 'NULL: True';
ELSE
RAISE NOTICE 'NULL: False';
END IF;
RAISE NOTICE 'end';
END
Результатом будет:
NOTICE: str: False
NOTICE: int: False
NOTICE: NULL: False
NOTICE: end
Неопределенное значение результата операции сравнения с NULL интерпретируется конструкцией IF NULL THEN ELSE END как False. Поэтому при простом сравнении переменной (которая может принимать NULL) с какой-либо константой можно проверки на NULL не делать:
IF _var IS NOT NULL AND _var > 0 THEN
-- можно заменить на
IF _var > 0 THEN
IF _var IS NOT NULL AND _var = 'token' THEN
-- можно за менить на
IF _var = 'token' THEN
...
В общем, будьте внимательны.