Сравнение строк в PostgreSQL без учета регистра: методы

Быстрый ответ

Для сравнения строк без учета регистра в PostgreSQL вы можете использовать оператор ILIKE или функцию LOWER():

Пример использования ILIKE:

SELECT * FROM table WHERE name ILIKE 'john';

Пример применения функции LOWER:

SELECT * FROM table WHERE LOWER(name) = 'john';

Оба этих способа позволяют приводить строки к одному регистру, что обеспечивает надежное и эффективное проведение сравнения.

Подробное изложение: Citext, индексы и экранирование

Чудесный Citext

Используйте тип данных CITEXT, предоставленный PostgreSQL, для более простого осуществления сравнений без учета регистра:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
    email CITEXT PRIMARY KEY,
    name VARCHAR(100)
);

При использовании CITEXT сравнение строк осуществляется автоматически:

INSERT INTO users (email, name) VALUES ('Test@Example.com', 'John Doe');

SELECT * FROM users WHERE email = 'test@example.com';

Индексы: когда требуется скорость

Для ускорения поиска используйте индекс на основе функции LOWER() или создайте триграммный индекс:

CREATE INDEX idx_lower_name ON users (LOWER(name));

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_gin_name ON users USING gin (name gin_trgm_ops);

Знаки подстановки и экранирование

Для обеспечения корректной работы с символами подстановки применяйте функцию replace(), которая служит для эскейпинга:

SELECT * FROM users WHERE replace(name, '!', '!!') ILIKE '%john%';

Для достого точного сопоставления строк предпочтительно использовать COLLATE:

ALTER TABLE users ALTER COLUMN email TYPE CITEXT COLLATE "en-US-x-icu";

Визуализация

Сравнение строк без учета регистра напоминает забавную аналогию с героями в масках — они внешне могут быть разными, но сущность у них общая.

Исходный список: ['Batman', 'batman', 'BATMAN'] Независимый от регистра список 💬🔍🎭

Примеры поиска героя под именем Бэтмен:

SELECT * FROM heroes WHERE LOWER(hero_name) = LOWER('batman');

Результат поиска: ['Batman', 'batman', 'BATMAN'] 👀

Решаем сложности: типы данных, ограничения и сортировка

Танцуем с типами данных

CITEXT упрощает процесс благодаря возможности неявного приведения типов:

SELECT * FROM users WHERE email = 'TEST@example.com';

Играем с уникальными ограничениями

Триграммные индексы обеспечивают ускорение поиска и поддержку уникальности:

CREATE UNIQUE INDEX idx_unique_email_trgm ON users USING gin (email gin_trgm_ops);

Сортировка и сопоставление

Используйте коллации ICU для детализированного сравнения и сортировки:

CREATE COLLATION ci_collation (provider = 'icu', locale = 'en-u-ks-primary', deterministic = false);
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR COLLATE ci_collation;

SELECT * FROM users ORDER BY name COLLATE "en-US-x-icu";

Полезные материалы

1. PostgreSQL: Документация: 9.7. Сопоставление шаблонов — прочтите подробнее о работе операторов ILIKE и LIKE.
2. Как выполнить регистронезависимый запрос в PostgreSQL? — ознакомьтесь с мнениями и подходами знатоков сообщества.
3. PostgreSQL: Документация: F.10. citext — здесь можно найти рекомендации по использованию CITEXT.
4. PostgreSQL: Документация: 11.7. Индексы на выражениях — эта страница содержит инструкции по оптимизации поиска.
5. Сопоставление шаблонов – PostgreSQL wiki — ознакомьтесь с подробностями о сопоставлении шаблонов.
6. PostgreSQL: Документация: 24.2. Поддержка коллаций — прочтите обзор по теме коллаций в PostgreSQL.