[SQL] How can I sort by Unicode in PostgreSQL?

I'm using PostgreSQL 16 and I my column is en_US.utf8, as in:

create table t (a varchar(10) collate "en_US.utf8");

insert into t (a) values ('a'), ('b'),('{a'), ('{b'),
('{a{a'), ('{a{b'), ('{aa'), ('{ab');


Then, if I sort by the column "a":

select * from t order by a;


I get:

{a
a
{a{a
{aa
{a{b
{ab
{b
b


I was expecting "a" before "{a" since "a" has the codepoint 97 and "{" has the codepoint 123.

Do I need to use a different collation to get Unicode sorting?

My use case uses longer strings, but I think this simple example illustrates the issue I'm having.

Continue reading...