作者
digoal
日期
2019-11-12
标签
PostgreSQL , citext , 忽略大小写 , 大小写不敏感 , operator覆盖
背景
忽略大小写，大小写不敏感。
方法1 - citext类型
postgres=> create extension citext ;
CREATE EXTENSION
postgres=> create table abc (id int, info citext);
CREATE TABLE
postgres=> insert into abc values (1,'HelloworD');
INSERT 0 1
postgres=> select * from abc where info='helloword';
id | info
----+-----------
1 | HelloworD
(1 row)
方法2 - 覆盖=操作符
```
postgres=> create or replace function ci_cmp(text,text) returns boolean as $$
postgres$> select lower($1)=lower($2);
postgres$> $$ language sql strict immutable;
CREATE FUNCTION
postgres=> create operator = (function=ci_cmp , leftarg=text , rightarg=text);
CREATE OPERATOR
postgres=> select 'hello' OPERATOR(public.=) 'Hello';
?column?
t
(1 row)
```
方法3， mchar mvarchar插件 by postgrespro
https://postgrespro.com/docs/enterprise/12/mchar
``` It implements types MCHAR and MVARCHAR, which are bug-to-bug compatible with MS SQL CHAR and VARCHAR respectively. Additionally, these types use libicu for comparison and case conversion, so their behavior is identical across different operating systems.
Postgres Pro also includes citext extension which provides types similar to MCHAR. But this extension doesn't emulate MS-SQL behavior concerning end-of-value whitespace.
Differences from Postgres Pro standard CHAR and VARCHAR are:
Case insensitive comparison
Handling of the whitespace at the end of string
These types are always stored as two-byte unicode value regardless of database encoding. ```
参考
https://www.postgresql.org/docs/12/citext.html
https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-PRECEDENCE
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
