0

I would like to use icu collation in PostgreSQL with rails application. I specified icu collation at database.yml like this,

  adapter: postgresql
  ctype: ja-x-icu
  collation: ja-x-icu

but I got the following error:

Caused by:
PG::WrongObjectType: ERROR:  invalid locale name: "ja-x-icu"

I found some questions which said that we cannot use icu collation in "CREATE DATABASE".
Get und-x-icu as collation and character type in Postgres 10 and win server 2008

Is this situation still the same as this question now?
If so, how can I create database with icu collation?

Thank you in advance. (I'm using Rails 7 and Postgres 11 but I can move to further version if necessary.)

user16012143
  • 139
  • 7
  • 4
    This is supported since Postgres 15 –  Dec 19 '22 at 14:33
  • 1
    Postgres 11 is almost 5 years old at this point. Move on. – max Dec 19 '22 at 14:38
  • 1
    Does this answer your question? [use postgresql image with icu collation in circieci (cimg/postgresql, rails)](https://stackoverflow.com/questions/74846348/use-postgresql-image-with-icu-collation-in-circieci-cimg-postgresql-rails) – engineersmnky Dec 19 '22 at 14:52
  • Oh, thanks! Now I got the picture. But still, although I started to use cimg/postgres:15 (which is circleci image for postgres 15), I got the same error about icu collation. Did I miss anything? – user16012143 Dec 19 '22 at 14:59

1 Answers1

3

That is only supported with PostgreSQL v15 or better.

If you are using v15 or better, I guess your mistake is that you simply used

initdb --encoding=UTF8 --locale=ja-x-icu datadir
The files belonging to this database system will be owned by user "laurenz".
This user must also own the server process.

initdb: error: invalid locale name "ja-x-icu"

You have to do that differently:

initdb --encoding=UTF8 \
   --locale-provider=icu --locale=ja_JP.utf8 --icu-locale=ja-x-icu datadir

Use --locale for the C library locale. Even if you are using an ICU collation (--icu-locale=ja-x-icu --locale-provider=icu), you have to specify a C library locale as well.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263