CODEWITHSUNDEEP
postgresqlsortingsql-order-by
jlars62
jlars62

Reputation: 7383

Postgresql order by returning two different orders

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?

Upvotes: 0

Views: 1004

Answers (1)

klin
klin

Reputation: 121919

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.

Upvotes: 3

Related Questions