F.8. citext

citext模块提供了一种大小写不敏感的字符串类型:citext。特别地,它在比较值时内部调用的是lower。除此之外,它的行为几乎与text完全相同。

F.8.1. 基本原理

PostgreSQL中做大小写不敏感匹配的标准方法曾经是在比较值时使用lower函数,例如:

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

这工作得比较好,但是有一些缺点:

citext数据类型允许你在 SQL 查询中消除对lower的调用,并且允许一个主键是大小写无关的。就和text一样,citext是区域相关的,这意味着大写和小写字符的匹配依赖于数据库LC_CTYPE设置的规则。此外,这种行为和在查询中使用lower是一样的。但是因为它是由数据类型以透明的方式完成的,你不需要记住在你的查询中做任何特殊的事情。

F.8.2. 如何使用它

这里是一个简单的用法示例:

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

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

SELECT * FROM users WHERE nick = 'Larry';

即使nick列被设置为larry而查询是LarrySELECT语句也将只返回一个元组。

F.8.3. 串比较行为

citext执行比较时先将每一个串转换成小写形式(调用lower)然后正常地比较结果。因此,如果两个串通过lower产生相同的结果,它们就被认为是相等。

为了尽可能接近地模拟一种大小写不敏感的排序规则,一些串处理操作符和函数有citext相关的版本。例如,当应用到citext时,正则表达式操作符~~*会展现出相同的行为:它们都以大小写不敏感的方式匹配。!~!~*也是一样,以及LIKE操作符~~~~*,以及!~~!~~*。如果你想以大小写敏感的方式匹配,你可以把该操作符的参数造型成text

相似地,如果下列函数的参数是citext,它们会以大小写不敏感的方式执行匹配:

对于 regexp 函数,如果你想要以大小写敏感的方式匹配,你可以指定"c"标志来强制大小写敏感的匹配。否则,如果你想要大小写敏感的行为,你必须在使用这些函数之一之前造型到text

F.8.4. 限制

F.8.5. 作者

David E. Wheeler

受 Donald Fraser 的citext模块启发。