在“按...排序"中的不同行为包括:子句:Oracle vs. PostgreSQL [英] Different behaviour in "order by" clause: Oracle vs. PostgreSQL

问题描述

我有下表(在Oracle和PostgreSQL中创建并填充了它们):

I have the following table (created and populated them in Oracle and PostgreSQL):

> create table foo (a varchar(10));

我用值填充了它们，并且order by子句在PostgreSQL和Oracle中的行为有所不同(我认为版本与该问题无关)

I populated them with values, and order by clause is behaving differently in PostgreSQL and Oracle (I don't think versions are relevant to this question):

Oracle:

> select a, length(a) from foo order by a;
A       LENGTH(A)
---------- ----------
.1          2
01          2
1           1
1#0         3
1#1         3
1.0         3
1.1         3
10          2
11          2

9 rows selected.

我得到了我所期望的. .1之前的.1，因为.在ascii表中的0之前.

I get what I expect. .1 before 01, since . is before 0 in ascii table.

但是，在PostgreSQL中，我有:

However, in PostgreSQL I have:

=> select a, length(a) from foo order by a;
  a  | length 
-----+--------
 01  |      2
 1   |      1
 .1  |      2
 10  |      2
 1.0 |      3
 1#0 |      3
 11  |      2
 1.1 |      3
 1#1 |      3
(9 rows)

为什么有区别?我知道它可能与整理顺序或类似内容有关，但是我希望获得一些指向何处阅读有关它的更多信息.

Why the difference? I know it probably has something to do with collate order or similar, but I would like some pointers on where to read more about it.

更新:整理PostgreSQL数据库上的信息:

UPDATE: collate info on the PostgreSQL database:

Encoding: UTF8
Collante: en_US.UTF-8
Ctype: en_US.UTF-8 | 

谢谢！

推荐答案

Postgres只有两个内置排序规则:C和POSIX. 操作系统提供任何其他归类. 在许多采用UTF语言环境的linux系统上，在排序过程中，所有非字母数字字符都会被忽略.

Postgres has only two built-in collations: C and POSIX. Any other collations are provided by operating system. On many linux systems in UTF locales all non alphanumeric characters are ignored during sorting.

您可以使用collate C获得预期的结果:

You can obtain expected result using collate C:

select a, length(a) from foo order by a collate "C";

您可以在此答案中找到更详细的解释 .

You can find more detailed explanation in this answer.

这篇关于在“按...排序"中的不同行为包括:子句:Oracle vs. PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！