begriffs open source - ai-pg/blob - full-docs/txt/citext.txt

   1
   2 F.9. citext — a case-insensitive character string type #
   3
   4    F.9.1. Rationale
   5    F.9.2. How to Use It
   6    F.9.3. String Comparison Behavior
   7    F.9.4. Limitations
   8    F.9.5. Author
   9
  10    The citext module provides a case-insensitive character string type,
  11    citext. Essentially, it internally calls lower when comparing values.
  12    Otherwise, it behaves almost exactly like text.
  13
  14 Tip
  15
  16    Consider using nondeterministic collations (see Section 23.2.2.4)
  17    instead of this module. They can be used for case-insensitive
  18    comparisons, accent-insensitive comparisons, and other combinations,
  19    and they handle more Unicode special cases correctly.
  20
  21    This module is considered “trusted”, that is, it can be installed by
  22    non-superusers who have CREATE privilege on the current database.
  23
  24 F.9.1. Rationale #
  25
  26    The standard approach to doing case-insensitive matches in PostgreSQL
  27    has been to use the lower function when comparing values, for example
  28 SELECT * FROM tab WHERE lower(col) = LOWER(?);
  29
  30    This works reasonably well, but has a number of drawbacks:
  31      * It makes your SQL statements verbose, and you always have to
  32        remember to use lower on both the column and the query value.
  33      * It won't use an index, unless you create a functional index using
  34        lower.
  35      * If you declare a column as UNIQUE or PRIMARY KEY, the implicitly
  36        generated index is case-sensitive. So it's useless for
  37        case-insensitive searches, and it won't enforce uniqueness
  38        case-insensitively.
  39
  40    The citext data type allows you to eliminate calls to lower in SQL
  41    queries, and allows a primary key to be case-insensitive. citext is
  42    locale-aware, just like text, which means that the matching of upper
  43    case and lower case characters is dependent on the rules of the
  44    database's LC_CTYPE setting. Again, this behavior is identical to the
  45    use of lower in queries. But because it's done transparently by the
  46    data type, you don't have to remember to do anything special in your
  47    queries.
  48
  49 F.9.2. How to Use It #
  50
  51    Here's a simple example of usage:
  52 CREATE TABLE users (
  53     nick CITEXT PRIMARY KEY,
  54     pass TEXT   NOT NULL
  55 );
  56
  57 INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
  58 INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
  59 INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
  60 INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
  61 INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );
  62
  63 SELECT * FROM users WHERE nick = 'Larry';
  64
  65    The SELECT statement will return one tuple, even though the nick column
  66    was set to larry and the query was for Larry.
  67
  68 F.9.3. String Comparison Behavior #
  69
  70    citext performs comparisons by converting each string to lower case (as
  71    though lower were called) and then comparing the results normally.
  72    Thus, for example, two strings are considered equal if lower would
  73    produce identical results for them.
  74
  75    In order to emulate a case-insensitive collation as closely as
  76    possible, there are citext-specific versions of a number of
  77    string-processing operators and functions. So, for example, the regular
  78    expression operators ~ and ~* exhibit the same behavior when applied to
  79    citext: they both match case-insensitively. The same is true for !~ and
  80    !~*, as well as for the LIKE operators ~~ and ~~*, and !~~ and !~~*. If
  81    you'd like to match case-sensitively, you can cast the operator's
  82    arguments to text.
  83
  84    Similarly, all of the following functions perform matching
  85    case-insensitively if their arguments are citext:
  86      * regexp_match()
  87      * regexp_matches()
  88      * regexp_replace()
  89      * regexp_split_to_array()
  90      * regexp_split_to_table()
  91      * replace()
  92      * split_part()
  93      * strpos()
  94      * translate()
  95
  96    For the regexp functions, if you want to match case-sensitively, you
  97    can specify the “c” flag to force a case-sensitive match. Otherwise,
  98    you must cast to text before using one of these functions if you want
  99    case-sensitive behavior.
 100
 101 F.9.4. Limitations #
 102
 103      * citext's case-folding behavior depends on the LC_CTYPE setting of
 104        your database. How it compares values is therefore determined when
 105        the database is created. It is not truly case-insensitive in the
 106        terms defined by the Unicode standard. Effectively, what this means
 107        is that, as long as you're happy with your collation, you should be
 108        happy with citext's comparisons. But if you have data in different
 109        languages stored in your database, users of one language may find
 110        their query results are not as expected if the collation is for
 111        another language.
 112      * As of PostgreSQL 9.1, you can attach a COLLATE specification to
 113        citext columns or data values. Currently, citext operators will
 114        honor a non-default COLLATE specification while comparing
 115        case-folded strings, but the initial folding to lower case is
 116        always done according to the database's LC_CTYPE setting (that is,
 117        as though COLLATE "default" were given). This may be changed in a
 118        future release so that both steps follow the input COLLATE
 119        specification.
 120      * citext is not as efficient as text because the operator functions
 121        and the B-tree comparison functions must make copies of the data
 122        and convert it to lower case for comparisons. Also, only text can
 123        support B-Tree deduplication. However, citext is slightly more
 124        efficient than using lower to get case-insensitive matching.
 125      * citext doesn't help much if you need data to compare
 126        case-sensitively in some contexts and case-insensitively in other
 127        contexts. The standard answer is to use the text type and manually
 128        use the lower function when you need to compare case-insensitively;
 129        this works all right if case-insensitive comparison is needed only
 130        infrequently. If you need case-insensitive behavior most of the
 131        time and case-sensitive infrequently, consider storing the data as
 132        citext and explicitly casting the column to text when you want
 133        case-sensitive comparison. In either situation, you will need two
 134        indexes if you want both types of searches to be fast.
 135      * The schema containing the citext operators must be in the current
 136        search_path (typically public); if it is not, the normal
 137        case-sensitive text operators will be invoked instead.
 138      * The approach of lower-casing strings for comparison does not handle
 139        some Unicode special cases correctly, for example when one
 140        upper-case letter has two lower-case letter equivalents. Unicode
 141        distinguishes between case mapping and case folding for this
 142        reason. Use nondeterministic collations instead of citext to handle
 143        that correctly.
 144
 145 F.9.5. Author #
 146
 147    David E. Wheeler <david@kineticode.com>
 148
 149    Inspired by the original citext module by Donald Fraser.