citext简介
citext（Case–Insensitive Text）是磐维数据库自带的一个扩展插件，安装后提供一种忽略大小写的文本数据类型。
当你将某个字段的数据类型设置为 citext 时，在该字段上进行查询、比较、排序等操作时，数据库会自动忽略字母的大小写差异。这极大地简化了需要大小写不敏感业务的开发工作。
核心思想： 在存储时，数据以其原始大小写形式保存；但在比较时，系统会调用LOWER() 函数处理然后再进行比较。
为什么需要citext？
在没有 citext 的情况下，如果我们希望实现不区分大小写的查询，通常需要这样做：
-- 使用 LOWER() 或 UPPER() 函数
SELECT * FROM testtab WHERE LOWER(username) = LOWER('ZhaNg');
-- 使用 ILIKE 操作符 (PostgreSQL风格，PanweiDB也支持)
SELECT * FROM testtab WHERE username ILIKE 'ZhaNg';
这些方法存在一些痛点：
- 代码冗余： 需要在每个查询条件中都使用函数或特殊操作符。
- 性能问题： 对字段使用 LOWER() 函数会导致数据库无法利用该字段上的标准 B-tree 索引，除非你创建了对应的函数索引。
- 容易出错： 开发者可能会忘记使用这些方法，导致查询结果不符合预期。
citext 数据类型从数据模型层面解决了这个问题，使得不区分大小写的比较成为默认行为，让代码更简洁，并且在某些情况下更利于优化。
数据库环境检查
--检查数据库版本
postgres=# select pw_version();
pw_version
-----------------------------------------------------------------------------
(PanWeiDB_V2.0-S3.2.0_B01) compiled at 2025-07-06 23:17:49 commit 86db355 +
product name:PanWeiDB +
version:V2.0-S3.2.0_B01 +
commit:86db355 +
openGauss version:5.0.0 +
host:x86_64-pc-linux-gnu
(1 row)
--检查数据库兼容模式:A模式，即Oracle模式
postgres=# select datname,datcompatibility from pg_database;
datname | datcompatibility
-----------+------------------
template1 | A
template0 | A
panweidb | A
postgres | A
(4 rows)
postgres=#
实验环境使用的数据库版本是集中式PanWeiDB_V2.0-S3.2.0_B01，数据库兼容模式是A模式，即Oracle模式。
citext使用示例
创建citext扩展
create extension citext;
创建表并构造数据
--创建测试表
CREATE TABLE testtab (
username CITEXT ,
pass TEXT NOT NULL
);
--插入数据
INSERT INTO testtab VALUES ('Zhang', 'Pass123');
INSERT INTO testtab VALUES ('zHang', 'Pass123');
INSERT INTO testtab VALUES ('wang', 'Passabc');
执行过程如下：
postgres=# CREATE TABLE testtab (
postgres(# username CITEXT ,
postgres(# pass TEXT NOT NULL
postgres(# );
CREATE TABLE
postgres=# INSERT INTO testtab VALUES ('Zhang', 'Pass123');
INSERT 0 1
postgres=# INSERT INTO testtab VALUES ('zHang', 'Pass123');
INSERT 0 1
postgres=# INSERT INTO testtab VALUES ('wang', 'Passabc');
INSERT 0 1
postgres=# SELECT * FROM testtab;
username | pass
----------+---------
Zhang | Pass123
zHang | Pass123
wang | Passabc
(3 rows)
citext使用
--执行查询
SELECT * FROM testtab WHERE username= 'Zhang';
SELECT * FROM testtab WHERE username= 'ZhaNG';
SELECT * FROM testtab WHERE username= 'WANG';
执行过程如下：
--执行查询：无论传入任何大小写组合，都能命中
postgres=# SELECT * FROM testtab WHERE username= 'Zhang';
username | pass
----------+---------
Zhang | Pass123
zHang | Pass123
(2 rows)
postgres=# SELECT * FROM testtab WHERE username= 'ZhaNG';
username | pass
----------+---------
Zhang | Pass123
zHang | Pass123
(2 rows)
postgres=# SELECT * FROM testtab WHERE username= 'WANG';
username | pass
----------+---------
wang | Passabc
(1 row)
postgres=#
citext使用限制
1、citext的效率不如text，因为该数据类型对应的操作符函数和比较函数必须创建数据的拷贝并且将其转换为小写形式来进行比较。但它比使用lower进行大小写不敏感的匹配效率要略高。
2、仅适用于PG模式和Oracle模式，其他模式下创建citext扩展时会提示如下错误：
postgres=# create extension citext;
ERROR: create extension “citext” is not supported with B && MSSQL type DBCOMPATIBILITY
在B模式（Mysql兼容模式），可以使用使用 COLLATE utf8_general_ci 实现大小写不敏感：
postgres=# select datname,datcompatibility from pg_database;
datname | datcompatibility
-----------+------------------
template1 | B
template0 | B
panweidb | B
postgres | B
(4 rows)
--创建表，构造数据
CREATE TABLE testtab (
username VARCHAR(50) COLLATE utf8_general_ci ,
pass TEXT NOT NULL
);
INSERT INTO testtab1 VALUES ('Zhang', 'Pass123');
INSERT INTO testtab1 VALUES ('zHang', 'Pass123');
-- 查询时不区分大小写
postgres=# select * from testtab1 where username='zhang'; -- 能匹配 Zhang、zHang等
username | pass
----------+---------
Zhang | Pass123
zHang | Pass123
(2 rows)
postgres=#
总结
citext是磐维数据库中一个非常实用且强大的扩展，能够优雅地解决文本数据大小写敏感性问题，提升开发效率和数据的整洁性。
关于作者
网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、YCP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~
