PostgreSQL不正确的排序 [英] PostgreSQL incorrect sorting

问题描述

我使用PostgreSQL 9.3.3，我有一个表，其中的一列称为title（字符变化（50））。

I use PostgreSQL 9.3.3 and I have a table with one column named as title (character varying(50)).

当我执行以下查询时：

select * from test
order by title asc

我得到以下结果：

#
A
#Example

为什么 #Example位于最后一个位置？我认为 #Example应排在第二位。

Why "#Example" is in the last position? In my opinion "#Example" should be in the second position.

推荐答案

对文本进行排序（包括 char 和 varchar 以及 text 类型）取决于当前的排序规则。

Sort behaviour for text (including char and varchar as well as the text type) depends on the current collation of your locale.

请参阅先前密切相关的问题：

See previous closely related questions:

• PostgreSQL排序


• https://stackoverflow.com/q/21006868/398670

• PostgreSQL Sort
• https://stackoverflow.com/q/21006868/398670

如果要按ASCII值进行简单排序，而不是按照本地语言规则进行适当本地化的排序，请您可以使用 COLLATE 子句

If you want to do a simplistic sort by ASCII value, rather than a properly localized sort following your local language rules, you can use the COLLATE clause

select * 
from test
order by title COLLATE "C" ASC

或更改数据库collat​​i全局（需要转储和重新加载或完全重新索引）。在我的Fedora 19 Linux系统上，我得到以下结果：

or change the database collation globally (requires dump and reload, or full reindex). On my Fedora 19 Linux system, I get the following results:

regress=> SHOW lc_collate;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

regress=> WITH v(title) AS (VALUES ('#a'), ('a'), ('#'), ('a#a'), ('a#')) 
          SELECT title FROM v ORDER BY title ASC;
 title 
-------
 #
 a
 #a
 a#
 a#a
(5 rows)

regress=> WITH v(title) AS (VALUES ('#a'), ('a'), ('#'), ('a#a'), ('a#')) 
          SELECT title FROM v ORDER BY title COLLATE "C" ASC;
 title 
-------
 #
 #a
 a
 a#
 a#a
(5 rows)

PostgreSQL使用操作系统的排序规则支持，因此结果可能因主机操作系统而异操作系统。特别是，至少某些版本的Mac OS X的Unicode排序规则处理明显中断。

PostgreSQL uses your operating system's collation support, so it's possible for results to vary slightly from host OS to host OS. In particular, at least some versions of Mac OS X have significantly broken unicode collation handling.

这篇关于PostgreSQL不正确的排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！