PostgreSQL : Документация: 16: F.10. citext — тип данных для строк, нечувствительных к регистру : Компания Postgres Professional

F.10. citext — тип данных для строк, нечувствительных к регистру
Пред.	Наверх	Приложение F. Дополнительно поставляемые модули и расширения	Начало	След.

F.10. citext — тип данных для строк, нечувствительных к регистру #

F.10.1. Обоснование
F.10.2. Как его использовать
F.10.3. Поведение при сравнении строк
F.10.4. Ограничения
F.10.5. Автор

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

Подсказка

Вместо этого модуля имеет смысл использовать недетерминированные правила сортировки (см. Подраздел 24.2.2.4). Они позволяют осуществлять сравнение без учёта регистра, без учёта ударения и другие варианты сравнений, при этом более корректно обрабатывая особые случаи Unicode.

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

F.10.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.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()

Для функций с регулярными выражениями, если вам нужно регистрозависимое сопоставление, вы можете добавить флаг «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.
Подход с переводом строк в нижний регистр для сравнения не работает в некоторых особых случаях Unicode, например когда одной букве в верхнем регистре соответствуют две буквы в нижнем регистре. Поэтому в Unicode различаются понятия преобразование регистра (case mapping) и выравнивание регистра (case folding). Чтобы сравнение производилось корректно и в этих случаях, используйте вместо citext недетерминированные правила сортировки.

F.10.5. Автор #

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

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

Пред.	Наверх	След.
F.9. btree_gist — классы операторов GiST с поведением B-дерева	Начало	F.11. cube — тип данных для представления многомерных кубов

F.10. citext — a case-insensitive character string type
Prev	Up	Appendix F. Additional Supplied Modules and Extensions	Home	Next

F.10. citext — a case-insensitive character string type #

F.10.1. Rationale
F.10.2. How to Use It
F.10.3. String Comparison Behavior
F.10.4. Limitations
F.10.5. Author

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

Tip

Consider using nondeterministic collations (see Section 24.2.2.4) instead of this module. They can be used for case-insensitive comparisons, accent-insensitive comparisons, and other combinations, and they handle more Unicode special cases correctly.

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.10.1. Rationale #

The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example

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

This works reasonably well, but has a number of drawbacks:

It makes your SQL statements verbose, and you always have to remember to use lower on both the column and the query value.
It won't use an index, unless you create a functional index using lower.
If you declare a column as UNIQUE or PRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively.

The citext data type allows you to eliminate calls to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the rules of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower in queries. But because it's done transparently by the data type, you don't have to remember to do anything special in your queries.

F.10.2. How to Use It #

Here's a simple example of usage:

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';

The SELECT statement will return one tuple, even though the nick column was set to larry and the query was for Larry.

F.10.3. String Comparison Behavior #

citext performs comparisons by converting each string to lower case (as though lower were called) and then comparing the results normally. Thus, for example, two strings are considered equal if lower would produce identical results for them.

In order to emulate a case-insensitive collation as closely as possible, there are citext-specific versions of a number of string-processing operators and functions. So, for example, the regular expression operators ~ and ~* exhibit the same behavior when applied to citext: they both match case-insensitively. The same is true for !~ and !~*, as well as for the LIKE operators ~~ and ~~*, and !~~ and !~~*. If you'd like to match case-sensitively, you can cast the operator's arguments to text.

Similarly, all of the following functions perform matching case-insensitively if their arguments are citext:

regexp_match()
regexp_matches()
regexp_replace()
regexp_split_to_array()
regexp_split_to_table()
replace()
split_part()
strpos()
translate()

For the regexp functions, if you want to match case-sensitively, you can specify the “c” flag to force a case-sensitive match. Otherwise, you must cast to text before using one of these functions if you want case-sensitive behavior.

F.10.4. Limitations #

citext's case-folding behavior depends on the LC_CTYPE setting of your database. How it compares values is therefore determined when the database is created. It is not truly case-insensitive in the terms defined by the Unicode standard. Effectively, what this means is that, as long as you're happy with your collation, you should be happy with citext's comparisons. But if you have data in different languages stored in your database, users of one language may find their query results are not as expected if the collation is for another language.
As of PostgreSQL 9.1, you can attach a COLLATE specification to citext columns or data values. Currently, citext operators will honor a non-default COLLATE specification while comparing case-folded strings, but the initial folding to lower case is always done according to the database's LC_CTYPE setting (that is, as though COLLATE "default" were given). This may be changed in a future release so that both steps follow the input COLLATE specification.
citext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. Also, only text can support B-Tree deduplication. However, citext is slightly more efficient than using lower to get case-insensitive matching.
citext doesn't help much if you need data to compare case-sensitively in some contexts and case-insensitively in other contexts. The standard answer is to use the text type and manually use the lower function when you need to compare case-insensitively; this works all right if case-insensitive comparison is needed only infrequently. If you need case-insensitive behavior most of the time and case-sensitive infrequently, consider storing the data as citext and explicitly casting the column to text when you want case-sensitive comparison. In either situation, you will need two indexes if you want both types of searches to be fast.
The schema containing the citext operators must be in the current search_path (typically public); if it is not, the normal case-sensitive text operators will be invoked instead.
The approach of lower-casing strings for comparison does not handle some Unicode special cases correctly, for example when one upper-case letter has two lower-case letter equivalents. Unicode distinguishes between case mapping and case folding for this reason. Use nondeterministic collations instead of citext to handle that correctly.

F.10.5. Author #

David E. Wheeler <david@kineticode.com>

Inspired by the original citext module by Donald Fraser.

Prev	Up	Next
F.9. btree_gist — GiST operator classes with B-tree behavior	Home	F.11. cube — a multi-dimensional cube data type