citext

Модуль citext предоставляет тип данных для строк символов, не учитывающих регистр, citext. По сути, сравнивая значения, он вызывает внутри себя функцию lower. В остальном он ведет себя почти так же, как тип text.

Совет
Возможно, вместо этого модуля имеет смысл использовать недетерминированные правила сортировки (см. подраздел Недетерминированные правила сортировки). Их можно использовать для сравнения без учета регистра, без учета ударения и в других комбинациях; при этом они более корректно обрабатывают особые случаи Unicode.

Этот модуль считается «доверенным», то есть его могут устанавливать обычные пользователи с правом CREATE в текущей базе данных.

Обоснование

Стандартный способ выполнить сравнение строк без учета регистра в QHB заключается в использовании функции lower при сравнении значений, например

SELECT * FROM tab WHERE lower(col) = LOWER(?);

Этот подход работает довольно хорошо, но имеет ряд недостатков:

Операторы SQL становятся громоздкими, и нужно не забывать всегда обрабатывать функцией lower и столбец, и значение запроса.
Индекс не будет использоваться, если только не создать функциональный индекс с помощью функции lower.
Если объявить столбец как UNIQUE или PRIMARY KEY, неявно сгенерированный индекс будет чувствительным к регистру. Поэтому он бесполезен для регистронезависимого поиска и не будет обеспечивать уникальность без учета регистра.

Тип данных citext позволяет исключить вызовы lower в запросах SQL и позволяет сделать первичный ключ регистронезависимым. Тип citext учитывает локаль, так же, как тип text, что означает, что сравнение символов в верхнем и нижнем регистре зависит от настройки параметра LC_CTYPE для базы данных. Это поведение, опять же, идентично использованию lower в запросах. Но поскольку оно реализуется прозрачно типом данных, в самих запросах дополнительно не нужно ничего делать.

Как его использовать

Ниже приведен простой пример использования:

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );

SELECT * FROM users WHERE nick = 'Larry';

Оператор SELECT вернет один кортеж, несмотря на то, что в столбце nick указано значение larry, а в запросе — Larry.

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

Модуль citext выполняет сравнения, приводя каждую строку к нижнему регистру (как если бы была вызвана функция lower) и затем сравнивая результаты как обычно. Так, например, две строки считаются равными, если функция lower выдаст для них одинаковые результаты.

Чтобы как можно точнее имитировать правило сортировки без учета регистра, в этом модуле имеются специальные, citext-специфичные версии ряда операторов и функций для обработки строки. Так, например, операторы регулярных выражений ~ и ~* демонстрируют то же поведение, когда применяются к типу citext: они оба не учитывают регистр. Это же распространяется на операторы !~ и !~*, а также операторы LIKE ~~, ~~*, !~~ и !~~*. Если требуется, чтобы эти операторы учитывали регистр, можно привести их аргументы к типу text.

Точно так же, все следующие функции выполняют сопоставления без учета регистра, если их аргументы имеют тип citext:

regexp_match()
regexp_matches()
regexp_replace()
regexp_split_to_array()
regexp_split_to_table()
replace()
split_part()
strpos()
translate()

Для функций с регулярными выражениями, если требуется сопоставление с учетом регистра, можно добавить флаг «c», чтобы принудительно включить этот режим. Или, как вариант, привести аргумент к типу text, прежде чем вызвать одну из этих функций.

Ограничения

Смена регистра символов в citext зависит от настройки параметра LC_CTYPE для вашей базы данных. Таким образом, как будут сравниваться значения, определяется при создании базы данных. По определениям стандарта Unicode это сравнение не будет истинно регистронезависимым. В сущности, это означает, что если вас устраивает установленное правило сортировки, вас должны устраивать и сравнения citext. Но если в вашей базе данных хранятся данные на разных языках, пользователи одного языка могут получать неожиданные результаты запросов, если правило сортировки предназначено для другого языка.
К значениям данных или столбцам citext можно добавлять указание COLLATE. В настоящее время операторы citext принимают такое явное указание COLLATE при сравнении строк в нижнем регистре, но изначальное приведение к нижнему регистру всегда выполняется согласно настройкам LC_CTYPE базы данных (как если бы указывалось COLLATE "default"). Это может измениться в будущем, чтобы на обоих этапах учитывалось указание COLLATE во входных данных.
Тип citext не так эффективен, как text, потому что функции операторов и функции сравнения для B-дерева должны делать копии данных и переводить их в нижний регистр для сравнения. Кроме того, только тип text может поддерживать исключение дубликатов в B-дереве. Тем не менее с citext сопоставление без учета регистра реализуется немного эффективнее, чем с использованием lower.
Тип citext не особенно поможет, если вам нужно сравнивать данные с учетом регистра в одних контекстах, и без учета регистра — в других. Стандартное решение — использовать тип text и вручную применять функцию lower, когда нужно выполнить сравнение без учета регистра; это прекрасно работает, если регистронезависимое сравнение требуется выполнять изредка. Если же сравнение почти всегда должно быть регистронезависимым и только иногда проводится с учетом регистра, имеет смысл сохранять данные в виде citext и явно приводить столбец к типу text, когда требуется регистрозависимое сравнение. В любом случае, чтобы оба варианта поиска были быстрыми, вам понадобится два индекса.
Схема, содержащая операторы citext, должна находиться в текущем пути поиска (search_path; обычно это схема public); в противном случае будут вызываться обычные, чувствительные к регистру операторы для типа text.
Подход с переводом строк в нижний регистр для сравнения некорректно работает в некоторых особых случаях Unicode, например, когда одной букве в верхнем регистре соответствуют две буквы в нижнем регистре. По этой причине в Unicode различаются понятия преобразование регистра и выравнивание регистра. Чтобы сравнение производилось корректно и в этих случаях, используйте вместо citext недетерминированные правила сортировки.

Автор

Дэвид Е. Уилер (David E. Wheeler), david@kineticode.com

Разработку вдохновил оригинальный модуль citext авторства Дональда Фрейзера (Donald Fraser).