ILIKE and NOT ILIKE in aws redshift different from total

By : Elm
Source: Stackoverflow.com
Question!

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?

By : Elm


Answers

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

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

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



This video can help you solving your question :)
By: admin