Re: Unexpected behavior sorting strings

On 4/8/20 7:35 AM, Jimmy Thrasher wrote:
> I'm seeing some unexpected behavior when sorting some strings, and it indicates I don't fully understand how postgresql string sorting works.
>
> As I understand it, postgresql sorts strings roughly like strcmp does: character by character based on encoding value.
>
> In particular, I'm seeing the following. I would expect "< S" to come first, because "<" (0x3c) is less than ">" (0x3e).
>
> ```
> supercatdev=# select unnest(array['> N', '< S']) as s order by s;
> s
> -----
> > N
> < S
> (2 rows)
> ```
>
> I've broken this down further:
> ```
> supercatdev=# select '> N' < '< S';
> ?column?
> ----------
> t
> (1 row)
> ```
>
> Am I missing something about how sorting works?

I believe you are looking for 'C' collation:

test=# select unnest(array[('> N' collate "C") , ('< S' COLLATE "C")])
as s order by s;

s
-----
< S
> N
(2 rows)

For more information see:

https://www.postgresql.org/docs/12/collation.html

>
> Metadata:
> - postgresql 9.5.19, running on Ubuntu 16LTS
> - encoding, collate, and ctype are all UTF8 or en_US.UTF-8, as appropriate
>
> Thanks!
>
> Jimmy
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com