ILIKE Operator

Giriş

Açıklaması şöyle

ILIKE, a specific operator for PostgreSQL, has the same features as the LIKE operator but is case-insensitive. If special characters defined for the LIKE operator (% and _) are not found within the search text, ILIKE can be used as a case-insensitive equality operator. Although using it for equality checks is incorrect, ILIKE is the most suitable solution when case-insensitive pattern matching is desired. 

ILIKE için Trigram Index lazım

Açıklaması şöyle

If we must support ILIKE substring search, the best immediate fix is a trigram index:

Şöyle yaparız. 

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_username_trgm
ON users
USING GIN (username gin_trgm_ops);

Açıklaması şöyle

Why this works

Trigram indexes break strings into 3-character chunks

They allow PostgreSQL to index LIKE, ILIKE, and fuzzy searches

They drastically reduce scan time for substring queries

Örnek

Şöyle yaparız.

SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ILIKE '%winter%'

Örnek

Şöyle yaparız. İ

SELECT DISTINCT x.address ->> 'long_name' AS country_name
FROM  (
    SELECT jsonb_array_elements(b.address) AS address
    FROM   brand b
    WHERE  jsonb_typeof(b.address) = 'array'            -- !!!
   ) x
WHERE  x.address ->> 'types' ILIKE ANY (ARRAY['%country%'::text]);