Change sort order by locale collation
Consider this example:
select * from (values('Abildlunden'),('Æblerosestien'),('Agern Alle 1')) x(word)
order by word collate "en_GB";
PostgreSQL is expanding "Æ" into "AE"- which is a correct rule for English.
word
---------------
Abildlunden
Æblerosestien
Agern Alle 1
(3 rows)
However, in Danish the correct order will be
select * from (values('Abildlunden'),('Æblerosestien'),('Agern Alle 1')) x(word)
order by word collate "da_DK";
word
---------------
Abildlunden
Agern Alle 1
Æblerosestien
(3 rows)
By default Aidbox uses en_US.utf8 locale. Aidboxdb version 14.7 supports locale collation changes. To change cluster locale to Danish, use PostgreSQL locale variables:
docker-compose.yaml
aidbox-db:
image: "${PGIMAGE}"
pull_policy: always
ports:
- "${PGHOSTPORT}:5432"
volumes:
- "./pgdata:/data"
environment:
POSTGRES_USER: "${PGUSER}"
POSTGRES_PASSWORD: "${PGPASSWORD}"
POSTGRES_DB: "${PGDATABASE}"
LC_COLLATE: "da_DK.UTF-8"
LC_CTYPE: "da_DK.UTF-8"
...
If these variables were set before the first start of aidboxdb (i.e. when the cluster is not initialized yet), then PostgreSQL will set cluster (and database) locales from environment variables.
Hence the SQL request
select * from (values('Abildlunden'),('Æblerosestien'),('Agern Alle 1')) x(word)
order by word;
Will return the right order for Danish locale.
Last modified 18d ago