ILIKE and NOT ILIKE in aws redshift different from total

amazon-redshift, count, null, sql

Solution

Double-quotes are for identifiers: `"myColumn"` Single quotes are for values: `'value'`.

Your examples contradict those basic syntax rules.

Also, you did not consider `NULL` values, which neither qualify with:

item_name ilike 'blue'

nor with:

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_name IS NULL OR NULL)     AS item_name_null
FROM   t1;

`~~*` .. internal Postgres operator for `ILIKE` `!~~*` .. internal Postgres operator for `NOT ILIKE` (Careful: slightly different operator precedence.)

Problem

I ran three following queries in amazon redshift: ``` select count(*) from t1 ``` The count was 1554. ``` select count(*) from t1 where item_name ilike "blue" ``` The count was 62. ``` select count(*) from t1 where item_name not ilike "blue" ``` The count was 85. The last two (62 + 85) should equal 1554. What am I missing?

Original source