Предложение postgresql NOT ILIKE не включает значения нулевой строки

Здесь настройка на Postgresql 9.2.4:

CREATE TABLE table (
    id integer NOT NULL,
    some_text text
);

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

SELECT * FROM table WHERE some_text IS NULL;

Я возвращаю запись. Пока все хорошо.

Однако, когда я запрашиваю:

SELECT * FROM table WHERE some_text NOT ILIKE "%anything%';

Я обнаружил, что ничего не было возвращено. Почему это? Я ожидаю, что пустая или пустая строка будет "не похожа на anything".

Ответ 1

В SQL NULL ничем не отличается. И это не равнозначно чему-либо.

Другими словами, если я не скажу вам свое второе имя, и вы не говорите мне свое среднее имя, как узнать, имеют ли наши два средних имени одно и то же имя или разные имена? Мы не можем знать.

Это часто вызывает людей в SQL, потому что это "трехзначная логика". Выражение может быть TRUE, FALSE или UNKNOWN. Те из нас, кто знаком с булевой алгеброй, знают, что NOT TRUE - FALSE, а NOT FALSE TRUE.

Но сложная часть состоит в том, что NOT UNKNOWN по-прежнему НЕИЗВЕСТНО.

Итак, решение для вас либо всегда хранит ненулевую строку в вашем столбце, либо использует выражение для учета трехзначной логики:

SELECT * FROM table WHERE some_text NOT ILIKE "%anything%' OR some_text IS NULL;

Или:

SELECT * FROM table WHERE COALESCE(some_text, '') NOT ILIKE '%anything%';

PostgreSQL также поддерживает оператор с нулевым безопасностью:

SELECT * FROM table WHERE some_text IS DISTINCT FROM 'anything';

Но, к сожалению, это работает только для равенства, а не для LIKE/ILIKE с шаблонами и подстановочными знаками.

Ответ 2

Вы можете использовать COALESCE для достижения своей цели, например

SELECT * FROM table WHERE COALESCE(some_text,'') NOT ILIKE "%anything%';