Links

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)

Change locale collation

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.
Any _sort Search Parameter will also consider Danish sort order.
Last modified 5mo ago