In CI test runs I noticed that string sorting order changed after switching from a debian-based PostgreSQL docker image to one that is based on Alpine Linux.
Debian image sorting: bar Bar foo Foo
Alpine image sorting: Bar Foo bar foo
Explanation
Alpine Linux is a very slim linux distribution that results in small docker image sizes (roughly 100MB instead of 150MB), so it's a popular choice. However, it does not have all comman locales installed and does not use all locales that a user installs by default.
Postgres orders string columns based on the
collation of the databse (LC_ALL or LC_COLLATE)
Show archive.org snapshot
. On dev machines this usually is en_US.UTF-8 locale. Alpine seems to use locale C which has a different sorting.
Solution
There is
a github issue
Show archive.org snapshot
that suggests that starting with postgres:15-alpine one can set the locale-provider icu which can also handle UTF-8 locales.
I have not tested this approch. So either try this or switch back to a debian-based image (e.g. postgres:15) when you run into this problem.
