3

I don't understand why the default collation on my psqlserver 11 instance returns a different result than using with explicit collation.

Details: 

show LC_CTYPE;        -- de-AT
show LC_COLLATE;      -- de-AT

When I use the default collation I get an unexpected output:

demo=# SELECT unnest(array['a','B', 'A']) order by 1;
 unnest 
--------
 A
 B
 a
(3 rows)

When I add the default collation explicitly, I get the expected output:

demo=# SELECT unnest(array['a','B', 'A']) collate "de-AT-x-icu" ORDER BY 1;
 unnest 
--------
 a
 A
 B
(3 rows)

The collation de-AT-x-icu uses exactly the default values for LC_COLLATE and LC_COLTYPE shown above:

SELECT collname, collcollate, collctype
FROM pg_collation
where collname like '%de-AT%';

  collname   | collcollate | collctype 
-------------+-------------+-----------
 de-AT-x-icu | de-AT       | de-AT

So I think both queries should return the same result: What am I missing?

More info:

  • postgresql version 11.5
  • OS inside the Docker container:
    Linux 8660fb4cef84 4.9.184-linuxkit #1 SMP Tue Jul 2 22:58:16 UTC 2019 x86_64 Linux
TmTron
  • 273
  • 4
  • 17

2 Answers2

2

Let me guess: you are using Alpine Linux, which uses musl as its C library, which uses ICU collations by default.

It is known that the collations in Alpine Linux don't work quite as they should, which is the cause of the problem. I am not sure if that is PostgreSQL's fault or not.

At any rate, you should use a different Linux distribution that uses glibc.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
0

After reading lots of posts, docs and the great comments an answers here, I'll try to sum up the case (please leave a comment if something is wrong).

It turned out that @Laurenz Albe is right: I was using the docker image timescale/timescaledb:1.5.0-pg11 (Docker-Hub link).
I didn't think, that this could be based on alpine, because those images usually end in -alpine. But since this is only a convention, I should have checked their Dockerfile...

It seems that currently there is only the alpine-based timescale-db image available on dockerhub: see timescaledb-docker#78

The docs on docker-hub postgres mention that:

postgres:-alpine
This image is based on the popular Alpine Linux project,.. The main caveat to note is that it does use musl libc instead of glibc and friends, so certain software might run into issues depending on the depth of their libc requirements.

So the docker-image uses musl which has some differences to glibc. One important difference is that the os-command locale is not available.
When starting the docker-container with the init-db parameters: e.g. POSTGRES_INITDB_ARGS=--lc-collate=de-AT --lc-ctype=de-AT, there are warnings on the console: 

running bootstrap script ... ok
performing post-bootstrap initialization ... sh: locale: not found
2019-11-04 16:48:16.725 UTC [26] WARNING:  no usable system locales were found
ok

So the db actually told us correctly that the provided locale/collation is ignored.

What is quite confusing, is that the following statements still show the values set by the initdb parameters:

show LC_CTYPE;    -- de-AT
show LC_COLLATE;  -- de-AT

But these are not used per default as @Daniel Vérité pointed out in a comment.

By looking at the postgres-alpine Dockerfile we can see that the image is built with icu support enabled (--with-icu).
So when we use an icu collation directly in a query (e.g. collate "de-AT-x-icu") it will work, because this uses the statically linked icu lib and does not need to ask the os dependent glib-c.

TmTron
  • 273
  • 4
  • 17