Why does PostgreSQL apparently ignore null cells whenever using NOT LIKE?

Question

This set me back for a long time, puzzled by why the entirely wrong record was returned:

SELECT * 
FROM bookkeeping 
WHERE category = 'Food' 
  AND "our description" NOT LIKE 'Online ordering%' 
ORDER BY timestamp DESC 
LIMIT 1;

Only by sheer chance did I try this (which made it work):

SELECT * 
FROM bookkeeping 
WHERE category = 'Food' 
  AND ("our description" NOT LIKE 'Online ordering%' OR "our description" IS NULL) 
ORDER BY timestamp DESC 
LIMIT 1;

You see, the "our description" text field was null for the record I expected, and for some reason, this makes PG not even consider it for "NOT LIKE", even though a NULL column obviously is "not like" 'Online ordering%'.

I assume that this is yet another "by design" thing? However, it seems completely bizarre to me, and I keep running into similar issues where it just seems like the developers/designers just didn't think things through. (But I'm sure they did and probably considered something that I'm not aware of.)

It's too late for me at this point to ever switch databases, and I'm sure nothing better exists out there anyway, but sometimes it feels like PostgreSQL and I are fundamentally incompatible in how we think.

Answer 1

This is how NULL is defined (and mandated) in the SQL standard and has nothing to do with Postgres specifically. All other DBMS (that have some respect for the SQL standard) will behave the same.

Any comparison with a NULL value yields NULL because you can't compare something you know nothing about. This has nothing to do with LIKE. 42 > null also yields null just like null = 'Arthur'. This also extends to any expression using null, e.g.: 42 + null also yields null.

null isn't even considered equal to itself - because you can't know if two "unknown" values are the same, so null = null or null like null both yield null.

In the context of a WHERE clause "null" is the same as "not true", thus the rows aren't returned.

Some tips on dealing with NULL values can be found here

Apart from explicitly using IS NULL you can use coalesce() to return a non-null value to be used with the LIKE operator:

WHERE category = 'Food' 
  AND coalesce("our description",'') NOT LIKE 'Online ordering%' 

This blog post might also be interesting for you.