Double-quotes are for identifiers:
Single quotes are for values:
You examples contradict those basic syntax rules.
Also, you did not consider
NULL values, which neither qualify with
item_name ilike 'blue'
item_name not ilike 'blue'
What do you get for:
SELECT count(*) AS all_rows
, count(item_name ~~* 'blue' OR NULL) As item_name_blue
, count(item_name !~~* 'blue' OR NULL) As item_name_not_blue
, count(item_name) AS item_name_not_null
, count(item_nameIS NULL OR NULL) AS item_name_null
~~* .. internal Postgres operator shorthand for
!~~* .. internal Postgres operator shorthand for
(Careful: slightly different operator precedence.)