Сравнение с 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 в запросе индексы задействованы не будут.

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 вышеописанная проблема так же сохраняется, но не так остро. Хотя остаётся не менее коварной:

DO $$
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
...

В общем, будьте внимательны.

Дополнительные материалы

PostgreSQL Documentation: Comparison Operators

comments powered by Disqus