Skip to main content

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

note

Эта страница переведена при помощи нейросети GigaChat.

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

Совет

Рассмотрите возможность использования недетерминированных параметров сортировки вместо этого модуля. Они могут использоваться для нечувствительных к регистру сравнений, сравнений без учета акцентов и других комбинаций, а также правильно обрабатывают больше специальных случаев Unicode.

Этот модуль считается «надежным», то есть его может установить не суперпользователь, имеющий привилегию 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 для правильной обработки.