current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 6 down vote favorite

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".

share|improve this question

2 Answers 2

up vote 14 down vote accepted

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.

share|improve this answer
1  
I like you're explanation with unknowns, but I'm not sure how that connects the the SQL. Why is a WHERE null ILIKE '%anything%' unknown? –  applepie Aug 14 '13 at 23:17
    
"Young man, in mathematics you don't understand things. You just get used to them." -- John von Neumann –  Bill Karwin Aug 14 '13 at 23:23
1  
oooh the sass. Is that a hard follow-up question? –  applepie Aug 14 '13 at 23:28
1  
@applepie: May seem cocky to disagree with von Neumann, but this is actually easy to understand IMO. Imagine a black box. You don't know what's in it. The content is unknown. If you ask whether the content is like %anything%, you cannot be sure. The answer is unknown as well ... –  Erwin Brandstetter Aug 14 '13 at 23:35
    
ahh thanks Erwin. I knew what you said already, but I was confused as to why null == unknown. If null is not equal or unequal to anything, then it makes sense that it's unknown. –  applepie Aug 14 '13 at 23:53
up vote 2 down vote

You may use COALESCE to achieve your goal, like

SELECT * FROM table WHERE COALESCE(some_text,'') NOT ILIKE "%anything%';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.