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".
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.
WHERE null ILIKE '%anything%' unknown?
%anything%, you cannot be sure. The answer is unknown as well ...null == unknown. If null is not equal or unequal to anything, then it makes sense that it's unknown.You may use COALESCE to achieve your goal, like
SELECT * FROM table WHERE COALESCE(some_text,'') NOT ILIKE "%anything%';
anything isn't an empty string. If it is this query will return no rows.