PostgreSQL在使用initdb初始化資料庫時,提供了"本地化"的引數locale,如不指定該引數則預設為空,即使用OS的locale設定.
本地化設定對以下SQL特性有影響:
1.排序和比較操作 : Sort order in queries using ORDER BY or the standard comparison operators on textual data
2.內建函式 : The upper, lower, and initcap functions
3.模式匹配 : Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions
4.to_char相關函式 : The to_char family of functions
5.LIKE能否使用索引 : The ability to use indexes with LIKE clauses

**排序**
同樣的資料,使用不同的LC_COLLATE,SQL輸出不同:
```
postgres=# SELECT name FROM unnest(ARRAY['MYNAME', ' my_name', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "C";
        name        
--------------------
  my_name
 MYNAME
 my-image.jpg
 my-third-image.jpg
(4 rows)

postgres=# SELECT name FROM unnest(ARRAY['MYNAME', ' my_name', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "zh_CN";
        name        
--------------------
 my-image.jpg
  my_name
 MYNAME
 my-third-image.jpg
(4 rows)
```
collate指定為"C",則使用預設的字串的二進位制ASCII碼值進行對比,而指定是zh_CN則不是.

使用zh_CN其行為按不區分大小寫進行處理
```
postgres=# SELECT name FROM unnest(ARRAY['MYNAME1', ' my_name2', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "zh_CN";
        name        
--------------------
 my-image.jpg
 MYNAME1
  my_name2
 my-third-image.jpg
(4 rows)

postgres=# SELECT name FROM unnest(ARRAY['myname1', ' myname2', 'myimage.jpg', 'mythirdimage.jpg']) name ORDER BY name collate "zh_CN";
       name       
------------------
 myimage.jpg
 myname1
  myname2
 mythirdimage.jpg
(4 rows)
```
郵件列表中的解釋如下:
>The behavior of each collation comes from the operating system's own
libc, except for the C collation, which is based on the ordering
implied by strcmp() comparisons. Generally, most implementations have
the behavior you describe, in that they assign least weight of all to
caseness and whitespace, and somewhat more weight to punctuation. I
don't think that there is much that can be done about it in practice,
though in principal there could be a collation that has all the
properties you want.

**內建函式**
如initcap,在法語和C下面會有不同
```
postgres=#  select initcap('élysée' collate "C");
 initcap 
---------
 éLyséE
(1 row)

postgres=#  select initcap('élysée' collate "fr_FR");
 initcap 
---------
 Élysée
(1 row)
```
在中文語境下,全形字元的小寫字母會轉換為全形的大寫字母
```
postgres=# select initcap('ａ' collate "zh_CN");
 initcap 
---------
 Ａ
(1 row)

postgres=# select initcap('ａ' collate "C");
 initcap 
---------
 ａ
(1 row)

```
在LC_COLLATE下,只會對7F以下的ASCII字元生效,其他字元不生效

**模式匹配**
```
postgres=#  select 'élysée' ~ '^\w+$' collate "fr_FR";
 ?column? 
----------
 t
(1 row)

postgres=#  select 'élysée' COLLATE "C" ~ '^\w+$';
 ?column? 
----------
 f
(1 row)
```

**LIKE能否使用索引**
```
postgres=# CREATE TABLE t_sort (
postgres(#     a text COLLATE "zh_CN",
postgres(#     b text COLLATE "C");
CREATE TABLE
postgres=# 
postgres=# INSERT INTO t_sort SELECT md5(n::text), md5(n::text)
postgres-#     FROM generate_series(1, 1000000) n;

INSERT 0 1000000
postgres=# CREATE INDEX ON t_sort USING btree (a);
CREATE INDEX
postgres=# CREATE INDEX ON t_sort USING btree (b);
CREATE INDEX
postgres=# ANALYZE t_sort;
ANALYZE
postgres=# SELECT * FROM t_sort LIMIT 2;
                a                 |                b                 
----------------------------------+----------------------------------
 c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b
 c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c
(2 rows)

postgres=# explain SELECT * FROM t_sort WHERE a LIKE 'c4ca4238a0%';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Gather  (cost=1000.00..18564.33 rows=100 width=66)
   Workers Planned: 2
   ->  Parallel Seq Scan on t_sort  (cost=0.00..17554.33 rows=42 width=66)
         Filter: (a ~~ 'c4ca4238a0%'::text)
(4 rows)

postgres=# explain SELECT * FROM t_sort WHERE b LIKE 'c4ca4238a0%';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using t_sort_b_idx on t_sort  (cost=0.42..8.45 rows=100 width=66)
   Index Cond: ((b >= 'c4ca4238a0'::text) AND (b < 'c4ca4238a1'::text))
   Filter: (b ~~ 'c4ca4238a0%'::text)
(3 rows)
```
使用zh_CN不能用上索引,但使用C可以用上索引

**參考資料**
[Locale Support](https://www.postgresql.org/docs/current/locale.html)
[One more time about collation in PostgreSQL](https://simply.name/pg-lc-collate.html)
[What is the impact of LC_CTYPE on a PostgreSQL database?](https://dba.stackexchange.com/questions/94887/what-is-the-impact-of-lc-ctype-on-a-postgresql-database)
[Re: Problem with PostgreSQL string sorting](https://www.postgresql.org/message-id/CAH2-Wzk5NzT-Tsn%2BO%2BXLprGvPYD%2BUk61kWeU1yVJZb97pJNQTg%40mail.gmail.com)

PostgreSQL DBA(42) - locale

相關文章