通过返回两个不同的订单的Postgresql订单 [英] Postgresql order by returning two different orders

问题描述

我有一台运行在Beta上的PostgreSQL服务器，另一台在本地运行.在两者上，我都有一个名为profile的表，该表具有名为character varying (255)的类型为character varying (255)的列.我检查了数据库是否具有相同的值.

I have a PostgreSQL server running on beta and one running locally. On both, I have a table called profile with a column named name of type character varying (255). I have checked that the dbs have the same values.

奇怪的是，当我用order by name asc在profile表上进行选择时，我得到了不同的结果.因此，在我的本地数据库中，名称为(I)Contractor的配置文件是第一个，而名称为3B的beta配置文件是第一个.

The weird part is when I do a select on the profile table with order by name asc I am getting different results. So on my local db, profile with name (I)Contractor is first and on beta profile with name 3B is first.

因此，在我的本地数据库(上似乎出现在数字字符之前，反之亦然.知道这是怎么回事吗?不同版本的Postgresql的排序规则会有所不同吗?

So it seems on my local db ( comes before numeric characters and vice versa for beta. Any idea how this is happening? Would the sort rule be different for different versions of Postgresql?

推荐答案

此行为的原因可能在于两个服务器都在两个不同的操作系统(例如Gnu Linux和MS Windows)上运行.排序方法的差异是由于排序规则是由操作系统提供的.要获得相同的排序顺序，可以使用collate:

The reason for this behavior probably lies in the fact that the two servers run on two different operating systems (eg. Gnu Linux and MS Windows). The difference in the method of sorting is due to the fact that the collation is provided by operating system. To get the same sort order, you can use collate:

select name from profile order by name collate "C"

另请参见订单"中的不同行为by"子句:Oracle与PostgreSQL .

这篇关于通过返回两个不同的订单的Postgresql订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！