默认情况下Postgresql中字符比较是严格区分大小写的,然而在最新的Postgres 12中支持通过CREATE COLLATION 创建非确定性排序规则以支持忽略大小写比较。
创建忽略大小写的排序的Collation如下:
官方说法:CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
然而在Windows版本下尝试官方提供的该方法并不能忽略大小写,据网上的说法,Windows版本附带的ICU版本是一个很旧的版本,也许这就是原因。因而在Windows版本中创建忽略大小写的排序规则应该如下:
CREATE COLLATION case_insensitive ( provider = 'icu', locale = '@colStrength=secondary', deterministic = false );
select ('AA' COLLATE "case_insensitive")=('aa' COLLATE "case_insensitive"); --结果为true;
所有标准和预定义的排序规则都是确定性的,所有用户定义的排序规则在默认情况下都是确定性的。虽然不确定性排序提供了更正确的行为,特别是考虑到Unicode的强大功能及其许多特殊情况时,但它们也有一些缺点。最重要的是,它们的使用会导致性能损失。另外,对于非确定性排序,某些操作是不可能的,比如模式匹配操作。因此,它们只能在特别需要的情况下使用。
All standard and predefined collations are deterministic, all user-defined collations are deterministic by default. While nondeterministic collations give a more “correct” behavior, especially when considering the full power of Unicode and its many special cases, they also have some drawbacks. Foremost, their use leads to a performance penalty. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.