CODEWITHSUNDEEP
postgresqldockeralpine-linux
happy_yar
happy_yar

Reputation: 61

How to set up sorting data on postgres in alpine container?

I'm using a postgres:9-alpine docker container and sorting data in table by varchar does not work correctly.

First, sorted by register then alphabetically, and I just need to alphabetically. I understand the problem in alpine locales. How can I configure this?

example:

SELECT regexp_split_to_table('B a D c', ' ') ORDER BY 1;

shows: В,D,a,c

but I need: a,B,c,D

WITH foo AS (
SELECT regexp_split_to_table('B a D c', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.utf8";

ERROR: collation "en_US.utf8" for encoding "UTF8" does not exist

SELECT version():

PostgreSQL 9.6.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.2.1) 6.2.1 20160822, 64-bit

Upvotes: 1

Views: 803

Answers (1)

user330315
user330315

Reputation:

Give the result column an name and sort by e.g. lower():

SELECT c
from regexp_split_to_table('B a D c', ' ') as t(c)
ORDER BY lower(c);

In Postgres 10 you could use a case insensitive ICU collation instead.

Upvotes: 1

Related Questions