Combining concatenation with ORDER BY

Question

I have troubles in combining concatenation with order by in Postgre (9.1.9). Let's say, I have a table borders with 3 fields:

            Table "borders"
    Column     |         Type         | Modifiers 
---------------+----------------------+-----------
 country1      | character varying(4) | not null
 country2      | character varying(4) | not null
 length        | numeric              | 

The first two fields are codes of the countries and the third one is the length of the border among those countries.
The primary key is defined on the first two fields.

I need to compose a select of a column that would have unique values for the whole table, in addition this column should be selected in decreasing order. For this I concatenate the key fields with a separator character, otherwise two different rows might give same result, like (AB, C and A, BC).

So I run the following query:

select  country1||'_'||country2 from borders order by 1;

However in the result I see that the '_' character is omited from the sorting. The results looks like this:

 ?column? 
----------
 A_CH
 A_CZ
 A_D
 AFG_IR
 AFG_PK
 AFG_TAD
 AFG_TJ
 AFG_TM
 AFG_UZB
 A_FL
 A_H
 A_I
 .
 .

You can see that the result is sorted as if '_' doesn't exists in the strings.

If I use a letter (say 'x') as a separator - the order is correct. But I must use some special character that doesn't appear in the country1 and country2 fields, to avoid contentions.

What should I do, in order to make the '_' character to be taken into account during the sorting.

---

EDIT

It turned out that the concatenation has nothing to do with the problem. The problem is that the order by simply ignores '_' character.

Answer 1

Just order by the two columns:

SELECT  country1||'_'||country2 FROM borders ORDER BY country1, country2;

Unless you use aggregates or windows, PostgreSQL allows to order by columns even if you don't include them in the SELECT list.

As suggested in another answer you can also change the collation of the combined column but, if you can, sorting on plain columns is faster, especially if you have an index on them.

Answer 2

select country1 || '_' || country2 collate "C" as a
from borders
order by 1

sql fiddle demo

Notes according to discussion in comments:

1.) COLLATE "C" applies in the ORDER BY clause as long as it references the expression in the SELECT clause by positional parameter or alias. If you repeat the expression in ORDER BY you also need to repeat the COLLATE clause if you want to affect the sort order accordingly.

sql fiddle demo

2.) In collations where _ does not influence the sort order, it is more efficient to use fog's query, even more so because that one makes use of the existing index (primary key is defined on the first two fields).
However, if _ has an influence, one needs to sort on the combined expression:

sql fiddle demo

Query performance (tested in Postgres 9.2):
sql fiddle demo

PostgreSQL Collation Support in the manual.