Unexpected behavior sorting strings

From: "Jimmy Thrasher" <jimmy(at)jimmythrasher(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unexpected behavior sorting strings
Date: 2020-04-08 14:35:32
Message-ID: 410dddd6-bc66-48ca-ab9d-13889d94b62c@www.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-04-08 15:28:06 Re: Best method to display table information in predefined formats
Previous Message Pavel Stehule 2020-04-08 14:03:03 Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12