PostgreSQL 9.1在select语句中使用collate
我有一个postgresql 9.1数据库表，“en_US.UTF-8”：
CREATE TABLE branch_language
(
id serial NOT NULL,
name_language character varying(128) NOT NULL,
branch_id integer NOT NULL,
language_id integer NOT NULL,
....
)
属性name_language包含各种语言的名称。 该语言由外键language_id指定。
我创建了一些索引：
/* us english */
CREATE INDEX idx_branch_language_2
ON branch_language
USING btree
(name_language COLLATE pg_catalog."en_US" );
/* catalan */
CREATE INDEX idx_branch_language_5
ON branch_language
USING btree
(name_language COLLATE pg_catalog."ca_ES" );
/* portuguese */
CREATE INDEX idx_branch_language_6
ON branch_language
USING btree
(name_language COLLATE pg_catalog."pt_PT" );
现在，当我做选择时，我没有得到我期待的结果。
select name_language from branch_language
where language_id=42 -- id of catalan language
order by name_language collate "ca_ES" -- use ca_ES collation
这会生成一个名称列表，但不是按照我预期的顺序：
Aficions i Joguines
Agència de viatges
Aliments i Subministraments
Aparells elèctrics i il luminació
Art i Antiguitats
Articles de la llar
Bars i Restaurants
...
Tabac
Àudio, Vídeo, CD i DVD
Òptica
正如我预计的最后两个条目出现在列表中的不同位置。
创建索引的作品。 除非你想优化性能，否则我认为它们确实不是必须的。
然而，select语句似乎忽略了该部分：collate“ca_ES”。
我选择其他排序规则时也存在此问题。 我尝试过“es_ES”和“pt_PT”，但结果相似。
我无法在您的设计中找到缺陷。 我努力了。
语言环境和整理
我重新回顾了这个问题。 考虑一下sqlfiddle上的这个测试用例 。 它似乎工作得很好。 我甚ca_ES.utf8在本地测试服务器（Debian Squeeze上的PostgreSQL 9.1.6）中创建了locale ca_ES.utf8 ，并将该语言环境添加到我的数据库集群中：
CREATE COLLATION "ca_ES" (LOCALE = 'ca_ES.utf8');
在上面的sqlfiddle中我可以看到相同的结果。
请注意，排序规则名称是标识符，需要双引号才能保留CamelCase拼写，如"ca_ES" 。 也许你的系统中有其他语言环境存在一些混淆？ 检查您的可用排序规则：
SELECT * FROM pg_collation;
通常，整理规则是从系统区域设置派生的。 请阅读手册中的详细信息。 如果仍然得到不正确的结果，我会尝试更新您的系统并重新生成"ca_ES"的语言环境。 在Debian（和相关的Linux发行版）中，这可以通过以下方式完成：
dpkg-reconfigure locales
NFC
我还有一个想法： 非标准化的UNICODE字符串 。
难道你的'Àudio'实际上是'̀ ' || 'Audio' '̀ ' || 'Audio' ？ 那就是这个角色：
SELECT U&' 300A';
SELECT ascii(U&' 300A');
SELECT chr(768);
阅读更多关于维基百科中的尖锐口音。
您必须将SET standard_conforming_strings = TRUE为使用第一行中的Unicode字符串。
请注意，某些浏览器无法正确显示未标准化的Unicode字符，并且许多字体对于特殊字符没有适当的字形，因此您可能在此处看到任何内容或乱码。 但UNICODE允许这样的废话。 测试看看你得到了什么：
SELECT octet_length('̀A') -- returns 3 (!)
SELECT octet_length('À') -- returns 2
如果这就是你的数据库收缩了，你需要摆脱它或承受后果。 治疗方法是将您的字符串标准化为NFC。 Perl具有卓越的UNICODE-foo技能，您可以在一个plperlu函数中使用它们的库来在PostgreSQL中执行它。 我已经这样做了，以免我疯狂。
请阅读David Wheeler在这篇关于PostgreSQL中的UNICODE规范化的优秀文章中的安装说明。
在unicode.org上阅读关于Unicode规范化表格的所有细节。