postgresql NOT ILIKE clause does not include null string values

Question

Here's the setup on Postgresql 9.2.4:

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

Now we enter one record, with a null or empty string for some_text, so that when we query:

SELECT * FROM table WHERE some_text IS NULL; 

I get the entry back. So far so good.

However, when I query:

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

I find that nothing was returned. Why is that? I expect a null or empty string to be "not like anything".

Answer 1

In SQL, NULL is not equal to anything. Nor is it unequal to anything.

In other words, if I don't tell you my middle name, and you don't tell me your middle name, how can we know if our two middle names are the same name or different names? We can't know.

This often trips people up in SQL, because it's "tri-value logic." An expression can be TRUE, FALSE, or UNKNOWN. Those of us familiar with boolean algebra know that NOT TRUE is FALSE, and NOT FALSE is TRUE.

But the tricky part is that NOT UNKNOWN is still UNKNOWN.

So the solution for you is either always store a non-null string in your column, or else use an expression to account for tri-value logic:

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

Or:

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

PostgreSQL also supports a null-safe equality operator:

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

But unfortunately, this works only for equality, not for LIKE/ILIKE with patterns and wildcards.