F.10. citext — регистронезависимый строковый тип данных#
F.10. citext — регистронезависимый строковый тип данных #
Модуль citext
предоставляет тип символьной строки, нечувствительный к регистру, citext
. По сути, он внутренне вызывает функцию lower
при сравнении значений. В остальном он ведет себя практически так же, как text
.
Подсказка
Рассмотрите возможность использования недетерминированных правил сортировки (см. Раздел 22.2.2.4) вместо этого модуля. Они могут использоваться для сравнения без учета регистра, сравнения без учета ударений и других комбинаций, и они правильно обрабатывают больше специальных случаев Юникода.
Этот модуль считается "доверенным", то есть его можно установить
недоступным пользователям, у которых есть привилегия CREATE
в текущей базе данных.
F.10.1. Обоснование #
Стандартный подход к выполнению регистронезависимого сравнения в Tantor BE заключается в использовании функции lower
при сравнении значений, например.
SELECT * FROM tab WHERE lower(col) = LOWER(?);
Это работает довольно хорошо, но имеет несколько недостатков:
Он делает ваши SQL-запросы громоздкими, и вы всегда должны помнить использовать функцию
lower
как для столбца, так и для значения запроса.Он не будет использовать индекс, если вы не создадите функциональный индекс с использованием
lower
.Если вы объявляете столбец как
UNIQUE
илиPRIMARY KEY
, неявно созданный индекс будет учитывать регистр символов. Поэтому он бесполезен для поиска без учета регистра и не будет обеспечивать уникальность без учета регистра.
Тип данных citext
позволяет избежать вызовов функции lower
в SQL-запросах и позволяет использовать регистронезависимый первичный ключ. citext
учитывает локаль, так же как и text
, что означает, что сопоставление символов верхнего и нижнего регистра зависит от правил установленного для базы данных значения LC_CTYPE
. Опять же, это поведение идентично использованию функции lower
в запросах. Но поскольку это делается автоматически типом данных, вам не нужно помнить о выполнении каких-либо особых действий в ваших запросах.
F.10.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.10.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()
Для функций regexp, если нужно сопоставлять с учетом регистра, вы можете
указать флаг “c”, чтобы выполнить сопоставление с учетом регистра. В противном случае
необходимо привести к типу text
перед использованием одной из этих функций,
если нужно получить поведение с учетом регистра.
F.10.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
с учетом регистра.Подход к приведению строк к нижнему регистру для сравнения некорректно обрабатывает некоторые особые случаи Юникода, например, когда одна заглавная буква имеет два эквивалента в нижнем регистре. Юникод различает между преобразованием регистра и сворачиванием регистра по этой причине. Используйте недетерминированные правила сортировки вместо
citext
, чтобы обрабатывать это правильно.
F.10.5. Автор #
David E. Wheeler <david@kineticode.com>
Вдохновленный оригинальным модулем citext
от Дональда Фрейзера.