В чем разница LIKE и ILIKE в PostgreSQL

Что проверяют

Базовое знание различия и, главное, понимание последствий для производительности и безопасности поиска. Следом почти всегда спрашивают, будет ли ILIKE использовать индекс и как сделать быстрый регистронезависимый поиск.

Как отвечать

• LIKE: сопоставление с шаблоном с учётом регистра. % означает любую последовательность символов, _ один символ, экранирование по умолчанию обратной косой чертой, свой символ задаётся через ESCAPE;
• ILIKE: то же самое без учёта регистра. Это расширение PostgreSQL, в стандарте SQL его нет, и на другую СУБД такой запрос не перенесётся. Операторные формы: ~~ для LIKE и ~~* для ILIKE;
• регистр и локаль: сравнение без учёта регистра опирается на правила локали базы, поэтому для кириллицы результат зависит от того, с какой локалью она создана.

Дальше про индексы, ради них вопрос и задают:

• обычный B-tree индекс помогает только LIKE 'abc%' с якорем в начале, и то при локали C или индексе с text_pattern_ops;
• ILIKE и шаблоны вида '%abc%' обычным B-tree не обслуживаются. Для них ставят расширение pg_trgm и GIN-индекс с gin_trgm_ops, он работает и для LIKE, и для ILIKE;
• для точного регистронезависимого сравнения есть индекс по выражению lower(email), тип citext или недетерминированная коллация.

В C# через EF Core с провайдером Npgsql это EF.Functions.Like(u.Name, pattern) и EF.Functions.ILike(u.Name, pattern). В Dapper шаблон передаётся параметром, а не склейкой строки.

Где спотыкаются

Не экранируют пользовательский ввод. Параметр защищает от SQL-инъекции, но % и _ из поисковой строки остаются спецсимволами, и запрос из одного % вернёт всю таблицу.

Уверены, что индекс по колонке ускорит ILIKE. План покажет Seq Scan, пока нет триграммного индекса.

Пишут в LINQ u.Name.ToLower().Contains(q.ToLower()). В SQL получается lower() над колонкой, обычный индекс не работает, и нужен либо индекс по выражению, либо триграммы.