F.7. citext

F.7.1. Обоснование

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

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

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

• Операторы SQL становятся громоздкими, и нужно не забывать всегда обрабатывать функцией lower и столбец, и значение.

• Индекс не будет использоваться, если только дополнительно не создать функциональный индекс с функцией lower.

• Если вы объявляете столбец как UNIQUE или PRIMARY KEY, неявно создаваемый индекс будет чувствительным к регистру. Поэтому он бесполезен для регистронезависимого поиска, так же как он не будет обеспечивать уникальность без учёта регистра.

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

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

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

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

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

SELECT * FROM users WHERE nick = 'Larry';

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

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

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

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

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

• regexp_matches()

• regexp_replace()

• regexp_split_to_array()

• regexp_split_to_table()

• replace()

• split_part()

• strpos()

• translate()

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

F.7.4. Ограничения

• Смена регистра символов в citext зависит от параметра LC_CTYPE вашей базы данных. Таким образом, как будут сравниваться значения, определяется при создании базы данных. На самом деле, по определениям стандарта Unicode, это сравнение не будет истинно регистронезависимым. По сути это означает, что если вас устраивает установленное правило сортировки, вас должны устраивать и сравнения citext. Но если в вашей базе данных хранятся строки на разных языках, пользователи одного языка могут получать неожиданные результаты запросов, если правило сортировки предназначено для другого языка.

• Начиная с PostgreSQL версии 9.1, вы можете добавлять указание COLLATE к значениям данных или столбцам citext. В настоящее время операторы citext принимают во внимание такое явное указание COLLATE, сравнивая строки в нижнем регистре, но изначальное приведение в нижний регистр всегда выполняется согласно параметру LC_CTYPE базы данных (как если бы указывалось COLLATE "default"). Это может быть изменено в будущем, чтобы на обоих этапах учитывалось указание COLLATE во входных данных.

• Тип citext не так эффективен, как text, так как функции операторов и функции сравнения для B-дерева должны делать копии данных и переводить их в нижний регистр для сравнения. Однако он несколько эффективнее варианта с применением lower для получения регистронезависимого сравнения.

• Тип citext малополезен в ситуациях, когда вам нужно сравнивать данные без учёта регистра в одних контекстах, и с учётом регистра — в других. Обычно в таких случаях используют text и вручную применяют функцию lower, когда нужно выполнить сравнение без учёта регистра; это прекрасно работает, если регистронезависимое сравнение требуется выполнять относительно редко. Если же почти всегда сравнение должно быть регистронезависимым и только иногда регистрозависимым, имеет смысл сохранить данные в столбце типа citext, и явно приводить их к типу text для регистрозависимого сравнения. В любом случае, чтобы оба варианта поиска были быстрыми, вам потребуются два индекса.

• Схема, содержащая операторы citext, должна находиться в текущем пути search_path (обычно это схема public); в противном случае будут вызываться регистрозависимые операторы для типа text.

F.7.5. Автор

Дэвид Е. Уилер <david@kineticode.com>

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