F.8. citext
Модуль citext
предоставляет тип данных для строк, нечувствительных к регистру, citext
. По сути он сравнивает значения, вызывая внутри себя функцию lower
. В остальном он почти не отличается от типа text
.
Подсказка
Вместо этого модуля имеет смысл использовать недетерминированные правила сортировки (см. Подраздел 23.2.2.4). Они позволяют осуществлять сравнение без учёта регистра, без учёта ударения и другие варианты сравнений, при этом более корректно обрабатывая особые случаи Unicode.
Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE
в текущей базе данных.
F.8.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.8.2. Как его использовать
Простой пример использования:
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
.
F.8.3. Поведение при сравнении строк
Модуль 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
, прежде чем вызывать эту функцию.
F.8.4. Ограничения
Смена регистра символов в
citext
зависит от параметраLC_CTYPE
вашей базы данных. Таким образом, как будут сравниваться значения, определяется при создании базы данных. На самом деле, по определениям стандарта Unicode, это сравнение не будет истинно регистронезависимым. По сути это означает, что если вас устраивает установленное правило сортировки, вас должны устраивать и сравненияcitext
. Но если в вашей базе данных хранятся строки на разных языках, пользователи одного языка могут получать неожиданные результаты запросов, если правило сортировки предназначено для другого языка.Начиная с PostgreSQL версии 9.1, вы можете добавлять указание
COLLATE
к значениям данных или столбцамcitext
. В настоящее время операторы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 различаются понятия преобразование регистра (case mapping) и выравнивание регистра (case folding). Чтобы сравнение производилось корректно и в этих случаях, используйте вместо
citext
недетерминированные правила сортировки.
F.8.5. Автор
Дэвид Е. Уилер <david@kineticode.com>
Разработку вдохновил оригинальный модуль citext
Дональда Фрейзера.