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、数据排序中的大小写敏感

排序也是Oracle一样，默认是区分大小写的。

例如：

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)

使用不同的collate字符串进行比较的结果竟然不一样。
因为上述字符串比较使用的是text_gt函数。当collate为C时，就是使用memcmp直接对被比较的字符串的比较，而collate为其它时，则会使用使用strcoll_l或strcoll进行比较。

/*      * 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)

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

总结：

PostgreSQL中和Oracle一样是大小写不敏感的，不同的是其默认是将字符转换成小写。
目前pg中没有参数去从数据库级别设置大小写敏感，但是针对不同的场景我们还是可以利用pg自身强大的功能来解决。

参考链接：

https://www.postgresql.org/docs/14/citext.html
https://www.postgresql.org/docs/14/sql-createcollation.html