PostgreSQL同locale多collation下数据库创建的选择与相关疑问

背景 #

我在Azure上创建了一个PostgreSQL数据库，该数据库包含一组预定义的**确定性（deterministic）**类型collation，其中有一个名为en-GB-x-icu、locale为en-GB的collation。

创建非确定性Collation #

执行以下SQL语句创建新的非确定性collation：

create collation en_gb_case_insensitive (
    provider = icu,
    locale = 'en-GB',
    deterministic = false
);

查询现有ICU类型Collation #

执行查询语句：

select 
collname,
collprovider,
collisdeterministic,
colliculocale,
collicurules
from 
pg_collation 
where 
collprovider = 'i' and
colliculocale like '%en-GB%'  

得到结果：

创建新数据库 #

执行以下语句创建数据库：

create database db_test002 
LOCALE_PROVIDER icu ICU_LOCALE 'en-GB' LOCALE 'en_GB.utf8' 
TEMPLATE template0;

查询新数据库信息 #

执行查询：

select datname, 
       datcollate,
       datctype,
       daticulocale,
       datcollversion
from pg_database
where datname = 'db_test002';

得到结果：

问题与解答 #

主问题：新数据库将使用哪个collation？ #

新数据库会使用预定义的**en-GB-x-icu**确定性collation。PostgreSQL通过ICU_LOCALE 'en-GB'匹配时，会优先选择已存在的确定性collation作为数据库默认collation，而非手动创建的非确定性版本。

辅助问题解答 #

1. 新数据库的collation是否为确定性？
   是。数据库关联的en-GB-x-icu属于确定性collation，且PostgreSQL默认会选择确定性collation作为数据库默认选项。

2. 如何精准指定想要使用的collation？
   创建数据库时，直接通过COLLATION参数明确指定目标collation名称，而非仅依赖ICU_LOCALE匹配。示例：

create database db_test002 
LOCALE_PROVIDER icu 
COLLATION 'en_gb_case_insensitive'
TEMPLATE template0;

通过这种方式可以精准指定使用手动创建的非确定性collation。

3. 创建locale仅为en-GB的非确定性collation是否有意义？
   有意义，但无法直接作为数据库默认collation使用。你可以在数据库创建后，将该collation指定给特定表、列或查询语句，满足特定场景下的大小写不敏感排序/比较需求。

编辑补充认知验证 #

我的几点认知：

• 创建新数据库前先创建新collation并无意义：确实，数据库创建时不会自动关联手动创建的同locale非确定性collation，仍会选择默认的确定性版本。
• PostgreSQL会通过ICU_LOCALE从现有collation中找到“最佳匹配”项：正确，匹配时优先选择确定性的预定义collation。
• 默认collation始终为确定性类型：正确，PostgreSQL默认会选择确定性collation作为数据库的默认collation。

我创建的不同ICU locale数据库示例：

• 我可以在数据库创建后创建与数据库locale匹配的非确定性collation，但这不会改变任何内容：正确，数据库创建后再创建的collation不会自动替换数据库默认collation，只能手动应用到对象或查询中。

内容的提问来源于stack exchange，提问作者Grzegorz W