PostgreSQL 大小写敏感的那些事
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
