我在Debian上运行了Postgres 9.4.4,并得到以下ORDER BY行为:

veure_test=# show LC_COLLATE;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table 
-----------------------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)


uname -a

Linux ---- 3.2.0-4-amd64 #1 SMP Debian 3.2.65-1 x86_64 GNU/Linux


但是,在我的iMac上,使用Postgres 9.3.4,我得到以下信息:

veure_test=# show LC_COLLATE;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table 
-----------------------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)


uname -a

Darwin ---- 14.4.0 Darwin Kernel Version 14.4.0: Thu May 28 11:35:04 PDT 2015; root:xnu-2782.30.5~1/RELEASE_X86_64 x86_64


我对为什么Debian版本似乎不区分大小写而OS X版本却不区分大小写感到困惑。我缺少什么,或者我需要提供什么其他信息?

更新:在Mac上,pg_collation表显示我具有en_US.UTF-8排序规则,但是在Debian上,我具有en_US.utf8排序规则。因此,在我的Mac上:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.UTF-8";                                                                                                                                                                                      
    bar    
-----------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)


和在Debian上:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.utf8";
    bar    
-----------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)


所以en_US.UTF-8en_US.utf8有不同之处排序顺序?

评论

我没有Mac可以测试,因此我在这里黑暗中进行拍摄...是否有可能在Mac上未将字符串'D d a a c b CD电容器'转换为文本字段?即,尝试SELECT regexp_split_to_table('D d a A c b CD Capacitor':: text,'')ORDER BY 1;看看会发生什么...

结果相同。在其他新闻中,从pg_collat​​ion中选择*表示Debian框具有en_US.utf8,而OS X具有en_US.UTF-8。使用那些在各个框上显式强制排序规则将显示不同的排序顺序:(

而且我已经发布了一个更新,它可能解释了这个问题,但是对我来说,这只会加深神秘感。现在,我找到了这个:stackoverflow.com/questions/19967555/…和这个:stackoverflow.com/questions/27395317/…

不幸的是,Postgres使用了OS中的归类实现,这使这种行为依赖于OS(我个人认为是一个错误-无论OS是什么,DBMS的行为都应相同)。因此,可以归结为Debian和OSX之间的系统库差异

如果排序顺序与其余部分不一致,则Postgres与系统其他部分之间将存在分歧。我也更喜欢相同的行为,但是我不会称其为遵循系统区域设置的错误。最终,相同的语言环境在整个OS上的行为应相同。 Debian的语言环境似乎正确,Apple似乎有过错(除非有其他解释)。

#1 楼


那么en_US.UTF-8en_US.utf8的排序顺序不同吗?


不,它们都是相同的,只是命名约定不同。


>我对为什么Debian版本似乎不区分大小写而OS X版本不区分大小写感到困惑。


是的,您是正确的。这是Mac上的默认行为。排序规则在UTF8编码的任何BSD-ish操作系统(包括OSX)上均不起作用。

此处提供的参考证明是:

排序顺序问题(UTF8语言环境不起作用

正如a_horse_with_no_name所说,Postgres使用操作系统中的归类实现。在两种操作系统上都无法获得相同的结果。

如果您可能(我说可能)这样做:ORDER BY lower(fieldname)

评论


在可能较大的结果集上使用ORDER BY function()时,请务必验证性能-由于它停止将索引用于排序,因此几乎可以肯定会导致额外的排序操作(可能在磁盘上),并且可能会更改查询计划程序的方法更广泛地攻击您的查询。

– David Spillett
16 Mar 7 '16 at 10:05

@David Spillett:您对Order函数是正确的。我认为我的答案更集中于为什么OP在iMac和Debian中具有不同的排序方式。谢谢

–JSapkota
16 Mar 7 '16 at 10:33

是的,您的回答非常好,可以完全覆盖问题。尽管很容易忘记(并且人们经常这样做),但提到“在可能影响查询计划的更改后使用真实数据进行测试”已经成为我的习惯性反应(就像在任何有关备份的讨论中提到测试,等等)。对于刚接触数据库工作的人甚至都不知道。

– David Spillett
16 Mar 7 '16 at 11:56

@DavidSpillett我不会停止使用索引,它只会停止使用普通索引。如果存在,将使用函数结果上的索引。在foo(Lower(bar))上创建索引l_bar;

–詹森
20 Jan 22'3:03



“在两种操作系统上都无法获得相同的结果。”这只是部分正确,或者至少对我来说,这种说法是一种误导。如果将LC_COLLATE设置为'C'(Ascii),则输出应相同。

–蒂姆·马里奇(Tim Malich)
20/11/15在13:08