citext — регистронезависимый строковый тип данных#

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

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

Обоснование#

Стандартный подход к выполнению нечувствительных к регистру совпадений в PostgreSQL заключался в использовании функции 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: они оба соответствуют без учета регистра. То же самое относится к !~ и !~*, а также к операторам 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. Но если есть данные на разных языках, хранящиеся в базе данных, пользователи одного языка могут обнаружить, что результаты их запросов отличаются от ожидаемых, если сортировка предназначена для другого языка.

• Начиная с 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 для правильной обработки.