When using WHERE clause with NOT LIKE on my_text_column, null values are skipped.

Why and how to get it ?

SELECT count(id) FROM public.mytable;
 count 
-------
    91
SELECT count(id) FROM public.mytable WHERE my_text_column LIKE 'foo';
 count 
-------
    59
SELECT count(id) FROM public.mytable WHERE my_text_column NOT LIKE 'foo';
 count 
-------
     0

CodePudding user response：

The SQL standard says this about NULL in Framework, 4.4.2:

[...] the null value is neither equal to any other value nor not equal to any other value – it is unknown whether or not it is equal to any given value [...]

In 4.4.3.3 it says:

A value of the Boolean data type is either true or false. The truth value of unknown is sometimes represented by the null value.

In its quirky way, it says that comparing something to the NULL values will result in NULL. Think of it this way: it is unknown if an unknown string is like foo.

There are several ways to get what you want:

1. Use coalesce:

   WHERE coalesce(my_text_column, 'foo') LIKE 'foo%'
   

2. Use an OR:

   WHERE my_text_column LIKE 'foo%' OR my_text_column IS NULL
   

3. use UNION ALL:

   SELECT count(id)
   FROM (SELECT id FROM public.mytable
         WHERE my_text_column LIKE 'foo%'
         UNION ALL
         SELECT id FROM public.mytable
         WHERE my_text_column IS NULL) AS subq;
   

Queries like that make indexing complicated.

CodePudding user response：

  SELECT count(*) FROM public.mytable 
WHERE my_text_colum not LIKE 'foo' or my_text_colum is null;

From manual:

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator.

So SELECT count(*) FROM tbl3 WHERE txt <> 'foo' or txt is null; also works.