Postgresql sorting mixed alphanumeric data

Question

Running this query:

select name from folders order by name

returns these results:

alphanumeric
a test
test 20
test 19
test 1
test 10

But I expected:

a test
alphanumeric
test 1
test 10
test 19
test 20

What's wrong here?

Answer 1

You can simply cast name column to bytea data type allowing collate-agnostic ordering:

SELECT name
FROM folders
ORDER BY name::bytea;

Result:

     name     
--------------
 a test
 alphanumeric
 test 1
 test 10
 test 19
 test 20
(6 rows)