浅谈PostgreSQL中大小写不敏感问题

CREATE COLLATION [ IF NOT EXISTS ] name (
  [ LOCALE = locale, ]
  [ LC_COLLATE = lc_collate, ]
  [ LC_CTYPE = lc_ctype, ]
  [ PROVIDER = provider, ]
  [ DETERMINISTIC = boolean, ]
  [ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

bill@bill=>create table test (c1 text); 
CREATE TABLE
bill@bill=>insert into test values ('a'),('b'),('c'),('A'),('B'),('C'); 
INSERT 0 6
bill@bill=>select * from test order by c1;
 c1 
----
 A
 B
 C
 a
 b
 c
(6 rows)

SQL> select * from test order by c1;

C1
--------------------------------------------------------------------------------
A
B
C
a
b
c

6 rows selected.

bill@bill=>select * from test order by c1 collate "zh_CN"; 
 c1 
----
 a
 A
 b
 B
 c
 C
(6 rows)

bill@bill=>CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false);
ERROR: ICU is not supported in this build
HINT: You need to rebuild PostgreSQL using --with-icu.

bill@bill=> CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false); 
CREATE COLLATION 
bill@bill=> select * from test order by c1 collate "case_insensitive"; 
 c1  
---- 
 a 
 A 
 b 
 B 
 c 
 C 
(6 rows)