How does PostgreSQL order textual values?

Question

The following query

SELECT * FROM (VALUES('c'), ('a'), ('b'), ('"a"')) X ORDER BY 1 ASC

produces

a
"a"
b
c

So how come does "a" appear after a even though it starts with a non alphabetic character (ie ") ?

I thought the output should be

"a"
a
b
c

It seems like PostgreSQL is stripping non alphabetic characters while sorting those values but that does't make sense to me.

Answer 1

Sort behaviour of text (char, varchar, text) depends on the current collation of your locale. Try using,

SELECT * 
FROM (VALUES('c'), ('a'), ('b'), ('"a"')) X(col) 
ORDER BY col COLLATE "C" asc;

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