0

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.

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.

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?

jlars62
  • 7,183
  • 7
  • 39
  • 60
  • Do both databases have the same encoding? Like ASCII vs. UTF-8 vs. something else? – mike.k Aug 10 '15 at 16:44
  • Run `SHOW LC_COLLATE` commands on both servers and compare their results. LC_COLLATE parameter affects the sort order of strings in ORDER BY. – krokodilko Aug 10 '15 at 16:59
  • @kordiko You were right. My values were `en_US.UTF-8` and `English_United States.1252`. Thanks – jlars62 Aug 10 '15 at 17:47

1 Answers1

2

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"

See also Different behaviour in “order by” clause: Oracle vs. PostgreSQL.

Community
  • 1
  • 1
klin
  • 112,967
  • 15
  • 204
  • 232
  • This is exactly what is happening. I am using windows and the beta server is on linux. Setting the `collate` fixed it! Thanks – jlars62 Aug 10 '15 at 17:45