PostgreSQL和Oracle一样，默认都是大小写不敏感的，但两者仍然存在区别：

• Oracle：默认是大小写不敏感，表名、字段名等不区分大小写，小写字母会自动转换为大写字母；
  需要用小写字母时需要使用双引号，或借助函數upper()和lower()；
• PostgreSQL：默认是大小写不敏感，表名、字段名等不区分大小写，大写字母会自动转换为小写字母；
  需要用大写字母时需要使用双引号，或借助函數upper()和lower()；

1、表、列名中的大小写敏感

例如我们创建表test，表名写成test、Test、TEST结果均是一样的：

bill@bill=>create table TEST(id int,info text); CREATE TABLE bill@bill=>\d test Table "public.test" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | info | text | | | 

列名也是同样如此：

讯享网bill@bill=>create table test(Id int,INFO text); CREATE TABLE bill@bill=>select id,info from test; id | info ----+------ (0 rows) 

那么如果我们想要指定表或者列名为大写该怎么办呢？
使用双引号即可。
但是可以看到这种方法也很麻烦，因为我们需要查询的时候也要用双引号，所以建议不要这么去使用。

bill@bill=>create table "TEST"(id int,info text); CREATE TABLE bill@bill=>select * from test; ERROR: relation "test" does not exist LINE 1: select * from test; ^ bill@bill=>select * from TEST; ERROR: relation "test" does not exist LINE 1: select * from TEST; ^ bill@bill=>select * from "TEST"; id | info ----+------ (0 rows) 

2、查询数据中的大小写敏感

当我们进行数据匹配查询时，是区分大小写的。

例如：

讯享网bill@bill=>insert into test values(1,'Bill'); INSERT 0 1 bill@bill=>select * from test where info = 'bill'; id | info ----+------ (0 rows) bill@bill=>select * from test where info = 'Bill'; id | info ----+------ 1 | Bill (1 row) 

这主要是因为pg查询中是否区分大小写取决于操作符，例如我们上面例子中用到的=号。

bill@bill=>\do+ = List of operators Schema | Name | Left arg type | Right arg type | Result type | Function | Description ------------+------+-----------------------------+-----------------------------+-------------+--------------------------+--------------- pg_catalog | = | "char" | "char" | boolean | chareq | equal pg_catalog | = | aclitem | aclitem | boolean | aclitemeq | equal pg_catalog | = | anyarray | anyarray | boolean | array_eq | equal pg_catalog | = | anyenum | anyenum | boolean | enum_eq | equal pg_catalog | = | anyrange | anyrange | boolean | range_eq | equal pg_catalog | = | bigint | bigint | boolean | int8eq | equal pg_catalog | = | bigint | integer | boolean | int84eq | equal pg_catalog | = | bigint | smallint | boolean | int82eq | equal pg_catalog | = | bit | bit | boolean | biteq | equal pg_catalog | = | bit varying | bit varying | boolean | varbiteq | equal pg_catalog | = | boolean | boolean | boolean | booleq | equal pg_catalog | = | box | box | boolean | box_eq | equal by area pg_catalog | = | bytea | bytea | boolean | byteaeq | equal pg_catalog | = | character | character | boolean | bpchareq | equal pg_catalog | = | cid | cid | boolean | cideq | equal pg_catalog | = | circle | circle | boolean | circle_eq | equal by area pg_catalog | = | date | date | boolean | date_eq | equal pg_catalog | = | date | timestamp with time zone | boolean | date_eq_timestamptz | equal pg_catalog | = | date | timestamp without time zone | boolean | date_eq_timestamp | equal pg_catalog | = | double precision | double precision | boolean | float8eq | equal pg_catalog | = | double precision | real | boolean | float84eq | equal pg_catalog | = | inet | inet | boolean | network_eq | equal pg_catalog | = | integer | bigint | boolean | int48eq | equal pg_catalog | = | integer | integer | boolean | int4eq | equal pg_catalog | = | integer | smallint | boolean | int42eq | equal pg_catalog | = | interval | interval | boolean | interval_eq | equal pg_catalog | = | jsonb | jsonb | boolean | jsonb_eq | equal pg_catalog | = | line | line | boolean | line_eq | equal pg_catalog | = | lseg | lseg | boolean | lseg_eq | equal pg_catalog | = | macaddr | macaddr | boolean | macaddr_eq | equal pg_catalog | = | macaddr8 | macaddr8 | boolean | macaddr8_eq | equal pg_catalog | = | money | money | boolean | cash_eq | equal pg_catalog | = | name | name | boolean | nameeq | equal pg_catalog | = | name | text | boolean | nameeqtext | equal pg_catalog | = | numeric | numeric | boolean | numeric_eq | equal pg_catalog | = | oid | oid | boolean | oideq | equal pg_catalog | = | oidvector | oidvector | boolean | oidvectoreq | equal pg_catalog | = | path | path | boolean | path_n_eq | equal pg_catalog | = | pg_lsn | pg_lsn | boolean | pg_lsn_eq | equal pg_catalog | = | real | double precision | boolean | float48eq | equal pg_catalog | = | real | real | boolean | float4eq | equal pg_catalog | = | record | record | boolean | record_eq | equal pg_catalog | = | smallint | bigint | boolean | int28eq | equal pg_catalog | = | smallint | integer | boolean | int24eq | equal pg_catalog | = | smallint | smallint | boolean | int2eq | equal pg_catalog | = | text | name | boolean | texteqname | equal pg_catalog | = | text | text | boolean | texteq | equal pg_catalog | = | tid | tid | boolean | tideq | equal pg_catalog | = | time with time zone | time with time zone | boolean | timetz_eq | equal pg_catalog | = | time without time zone | time without time zone | boolean | time_eq | equal pg_catalog | = | timestamp with time zone | date | boolean | timestamptz_eq_date | equal pg_catalog | = | timestamp with time zone | timestamp with time zone | boolean | timestamptz_eq | equal pg_catalog | = | timestamp with time zone | timestamp without time zone | boolean | timestamptz_eq_timestamp | equal pg_catalog | = | timestamp without time zone | date | boolean | timestamp_eq_date | equal pg_catalog | = | timestamp without time zone | timestamp with time zone | boolean | timestamp_eq_timestamptz | equal pg_catalog | = | timestamp without time zone | timestamp without time zone | boolean | timestamp_eq | equal pg_catalog | = | tsquery | tsquery | boolean | tsquery_eq | equal pg_catalog | = | tsvector | tsvector | boolean | tsvector_eq | equal pg_catalog | = | uuid | uuid | boolean | uuid_eq | equal pg_catalog | = | xid | integer | boolean | xideqint4 | equal pg_catalog | = | xid | xid | boolean | xideq | equal pg_catalog | = | xid8 | xid8 | boolean | xid8eq | equal public | = | citext | citext | boolean | citext_eq | (63 rows) 

其用到的函数为texteq，我们可以看下该函数的定义（src/backend/utils/adt/varlena.c）：

讯享网Datum texteq(PG_FUNCTION_ARGS) { 
    Oid collid = PG_GET_COLLATION(); bool result; check_collation_set(collid); if (lc_collate_is_c(collid) || collid == DEFAULT_COLLATION_OID || pg_newlocale_from_collation(collid)->deterministic) { 
    Datum arg1 = PG_GETARG_DATUM(0); Datum arg2 = PG_GETARG_DATUM(1); Size len1, len2; /* * Since we only care about equality or not-equality, we can avoid all * the expense of strcoll() here, and just do bitwise comparison. In * fact, we don't even have to do a bitwise comparison if we can show * the lengths of the strings are unequal; which might save us from * having to detoast one or both values. */ len1 = toast_raw_datum_size(arg1); len2 = toast_raw_datum_size(arg2); if (len1 != len2) result = false; else { 
    text *targ1 = DatumGetTextPP(arg1); text *targ2 = DatumGetTextPP(arg2); result = (memcmp(VARDATA_ANY(targ1), VARDATA_ANY(targ2), len1 - VARHDRSZ) == 0); PG_FREE_IF_COPY(targ1, 0); PG_FREE_IF_COPY(targ2, 1); } } else { 
    text *arg1 = PG_GETARG_TEXT_PP(0); text *arg2 = PG_GETARG_TEXT_PP(1); result = (text_cmp(arg1, arg2, collid) == 0); PG_FREE_IF_COPY(arg1, 0); PG_FREE_IF_COPY(arg2, 1); } PG_RETURN_BOOL(result); } 

可以看到，就是直接使用memcmp函数对字符直接进行比较，自然是会去区分大小写。所以想要不区分大小写除非我们数据写入的时候就不区分大小写。

因此我们可以使用citext模块来实现忽略大小写的查询：

bill@bill=>create extension citext ; CREATE EXTENSION bill@bill=>create table test(id int,info citext); CREATE TABLE bill@bill=>insert into test values(1,'Bill'); INSERT 0 1 bill@bill=>select * from test where info = 'bill'; id | info ----+------ 1 | Bill (1 row) 

3、数据排序中的大小写敏感

讯享网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) 

不过从pg12开始支持不区分大小写，或者区分大小写的排序的collate。

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 

其中几个关键参数：

讯享网

• PROVIDER：指定用于与此排序规则相关的区域服务的提供程序。可能的值是: icu、libc。 默认
  是libc。但若要设置大小写不敏感，目前只支持icu。
• DETERMINISTIC：设置成not deterministic表示大小写不敏感。

例子：

讯享网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) 

说到collate，不知道大家有没有遇过这种情况：

bill@bill=>select 'a' > 'A' collate "en_US"; ?column? ---------- f (1 row) bill@bill=>select 'a' > 'A' collate "C"; ?column? ---------- t (1 row) 

讯享网/* * Unfortunately, there is no strncoll(), so in the non-C locale case we * have to do some memory copying. This turns out to be significantly * slower, so we optimize the case where LC_COLLATE is C. We also try to * optimize relatively-short strings by avoiding palloc/pfree overhead. */ 

同理，我们使用索引查询时也需要注意：创建索引时用的collate和查询时的collate一致才可以使用到索引。

4、其它场景下的大小写敏感

当我们使用数组类型时，是否区分大小写呢？

bill@bill=>select 'a' = any(array['A','1']); ?column? ---------- f (1 row) 

可以看到和普通的字符串查询一样有区分大小写，那么我们该怎么去忽略大小写呢？

和字符串不同，我们没办法数据进行lower操作：

讯享网bill@bill=>select 'a' = lower(any(array['A','1'])); ERROR: syntax error at or near "any" LINE 1: select 'a' = lower(any(array['A','1'])); ^ 

不过我们可以自己写一个函数来将数据中的元素转换为小写。

bill@bill=>create or replace function lower(text[]) returns text[] as $$ bill$# select array_agg(lower(x)) from unnest($1) t(x); bill$# $$ language sql strict immutable; CREATE FUNCTION bill@bill=>select lower(array['A','a']); lower ------- {a,a} (1 row) 

再次查询：

讯享网bill@bill=>select 'a' = any( lower(array['A','1']) ); ?column? ---------- t (1 row) 

当然除此之外还有其它方法，例如自定义操作符等等。

总结：

参考链接：