PostgreSQL Sort

Question

I have a sort problem in PostgreSQL with below data:

name
-----
@CF
@CG
CD
CE

I used select name from table order by name, the result as below:

name
-----
CD
CE
@CF
@CE

It seems that Postgres just ignores the special character @ and sorts the left string. However, I'd like it sorted like this:

name
-----
@CF
@CG
CD
CE

Searching the internet didn't help. I hope someone here could give a suggestion.

Answer 1

Use PostgreSQL's collation support to tell it you want a particular collation.

Given:

CREATE TABLE t AS VALUES ('CD'),('CE'),('@CF'),('@CE');

You can force byte-wise collation using:

SELECT * FROM t ORDER BY column1 COLLATE "C";

The "C" collation is a byte-wise collation that ignores national language rules, encoding, etc.

Answer 2

Just add that to the order by clause:

ORDER BY CASE WHEN LEFT(name,1) = '@' THEN 0 ELSE 1 END ASC, name ASC