本文主要討論PostgreSQL中大小寫不敏感存在的問題。
默認情況下，PostgreSQL會將列名和表名全部轉換為小寫狀態。
圖1 Person與person
如圖1所示，我們創建表person，其中包含name列。然后插入一條記錄。執行SELECT查詢時，使用列名Name和表名Person而不是name和person，發現仍然可以正常獲取剛剛插入表person中的記錄。
圖2 創建表Person？
此時如果我們再想創建表Person，會得到一個錯誤，因為此時PostgreSQL實際上把表名從Person轉換成了person。由于已經存在表person，所以會報錯。
通常情況下，這種大小寫不敏感是很方便的，但是當我們想創建大小寫敏感的表名和列名（需要使用雙引號）時，會產生一些問題。
圖3 創建表Person
如圖3所示，我們成功創建了表Person，并插入了一條記錄，此條記錄和插入person中的不同以示區分。再次使用SELECT查詢，并且使用表名Person和列名Name，但是返回的結果卻是person中的記錄。這還是因為PostgreSQL將Person轉換成了person。所以想要正確查詢，需要使用“Person”和“Name”（如圖4所示）。
圖4 獲取表Person中的記錄
此時查看數據庫中的表（見圖5），可以發現Person和person這兩個表都在數據庫中。如果我們使用DROP TABLE Person，刪除的仍然是表person。
圖5 刪除操作
綜上所述，當創建表或者寫SQL查詢語句時，建議避免使用雙引號。
補充：PostgreSQL大小寫不敏感排序
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表示大小寫不敏感。
例子：
—正常情況的排序
我們可以看到，正常的order by會區分大小寫。
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)
同樣，在oracle中也是一樣：
SQL> select * from test order by c1;
C1
--------------------------------------------------------------------------------
A
B
C
a
b
c
6 rows selected.
—不區分大小寫排序
可以看到我們指定collate為zh_CN時便沒有區分大小寫排序。
bill@bill=>select * from test order by c1 collate "zh_CN";
c1
----
a
A
b
B
c
C
(6 rows)
我們也可以自定義collation支持不區分大小寫的排序，但是需要注意在編譯數據庫的時候加上 —with-icu才可以，否則會出現報錯：
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)
目前collate不支持=操作不區分大小寫，目前需要citext插件。
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL LIKE 大小寫實例
- Postgresql 數據庫轉義字符操作
- postgresql~*符號的含義及用法說明
- postgresql數據庫使用說明_實現時間范圍查詢
- postgresql 實現將數組變為行
- 基于PostgreSql 別名區分大小寫的問題
