How does PostgreSQL order textual values?

user_897041 0
Viewed 44

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.

sql-order-by postgresql sql
2 Answers

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.

user_93662770

There is something "funny" going on with the collation of the literal in the VALUES row value constructor, but if you use a "real" table, your query sorts as expected:

CREATE TABLE T (StringCol VARCHAR(5));
INSERT T Values ('a'),('b'),('c'),('"a"');
SELECT * FROM T ORDER BY 1

I took a quick look at this topic, but couldn't find an obvious reason, a more thorough read is needed, but you should find your answer there.

HTH

user_106998880
Related