citext

citext

citext 模块提供了一个不区分大小写的字符串数据类型, citext。 本质上，citext在比较值时在内部调用lower 函数。除此之外，它的操作几乎与text相同

对文本值执行不区分大小写匹配的方法是曾经是在比较值时使用 lower 函数，例如

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

这种方法可行, 但是有缺点：

  • 它会使SQL语句变得冗长, 而且总是在列查询值中同时使用lower函数。
  • 除非已经使用lower函数创建一个函数索引，否则该函数不适用于索引。

citext数据类型允许您在SQL查询中不调用lower函数，并且可以在citext类型的列上创建不区分大小写的主键。类似于ext类型，citext是本地化识别匹配, 这意味着大写和小写字母的匹配依赖于数据库LC_CTYPE设置规则。这和在查询中使用lower函数功能相同，但它由数据类型透明地完成，因此您不必在查询中执行任何特殊操作。

安装 citext

在使用citext类型之前，必须在要使用该类型的每个数据库中注册扩展名：

$ psql -d testdb -c "CREATE EXTENSION citext"

使用citext类型

以下是一个定义一个citext类型数据列的简单的例子:

CREATE TABLE users (
    id bigint PRIMARY KEY,
    nick CITEXT NOT NULL,
    pass TEXT   NOT NULL
) DISTRIBUTED BY (id);

INSERT INTO users VALUES (1,  'larry',  md5(random()::text) );
INSERT INTO users VALUES (2,  'Tom',    md5(random()::text) );
INSERT INTO users VALUES (3,  'Damian', md5(random()::text) );
INSERT INTO users VALUES (4,  'NEAL',   md5(random()::text) );
INSERT INTO users VALUES (5,  'Bjørn',  md5(random()::text) );

SELECT * FROM users WHERE nick = 'Larry';

SELECT语句将返回一个元组，尽管nick列被设置为larry并且查询指定为Larry

字符串比较操作

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类型。

局限性

  • citext类型的折叠操作依赖于数据库的LC_CTYPE设置。因此，在创建数据库时，应确定如何比较值。通过在Unicode标准定义的术语中并不是真正大小写不敏感的。实际上，只要您对您的排序规则感到满意，您应该对citext的比较感到满意。 但是，如果您的数据库中存储了不同语言的数据，则即使排序规则适用于其他语言，有一种语言的用户也可能会发现其查询结果与预期不符。

  • citext不如text高效，因为运算符函数和使用B树的比较函数必须拷贝一份数据并将它们转换为小写进行比较。但是，它比使用lower执行不区分大小写的匹配效率稍高一些。
  • 如果你需要数据比较在某些情况中大小写敏感但其他情况中大小写不敏感citext不一定是一个最好的选择，建议的做法是使用text类型，然后在需要大小写不敏感的情况下手动使用lower函数。这种方法适用于较少需要大小写不敏感比较的情况下。如果您需要的大小写不敏感比较的频率明显大于大小写敏感，您可以将数据存储为citext类型，当想要进行大小写敏感比较时将数据类型强制转换为text类型。如果您希望在两种情况下都能高效的进行，您需要建立两个索引。
  • 包含citext运算的模式必须在当前的search_path中（通常是public）如果不是这样的话，请调用大小写敏感的text运算。