postgresqlで「PG::UndefinedObject: ERROR: collation "ja_JP.utf8" for encoding "UTF8" does not exist」が出るとき

概要

本質的な原因や機構を正確に理解できていない状態で恐縮ですが、本エラーメッセージでGoogleやドキュメントを検索しても対策がみつからなかったため、どなたか（例：1週間前の自分）の助けになればと暫定対策ではありますが状況を共有します。

環境

Windows11 WSL2 Ubuntu 22.04.2 LTS
PostgreSQL 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1)

現象

Railsにて、以下のようにカナでソートする際に以下のようにエラーが出現した。

> Product.order(Arel.sql('name_kana collate "ja_JP.utf8"')) 
>-> ActiveRecord::StatementInvalid - PG::UndefinedObject: ERROR:  collation "ja_JP.utf8" for encoding "UTF8" does not exist

データベースの設定は以下の通り

postgres=# SHOW LC_COLLATE; 
 lc_collate 
------------
 C.UTF-8

 postgres=# SELECT datname, datcollate, datctype FROM pg_database WHERE datname = 'database';
  datname   | datcollate | datctype 
------------+------------+----------
 database | C.UTF-8    | C.UTF-8

OSのlocaleは下記の通りで ja_JP.utf8 は追加されている

postgres@pc:/home/pc$ locale -a | grep utf
C.utf8
ja_JP.utf8

暫定対策

以下のようにdatabaseごとにcollationを作成すると問題は解消した。

\c database;
CREATE COLLATION "ja_JP.utf8" (LOCALE = 'ja_JP.UTF-8');

railsの場合、テストDBと開発用DBそれぞれに実行する必要があるので要注意

（しかしこの結果は # show all; とかにも反映されていないようだし、mysqlにはある show collation のようなものも postgresql にはないのでなにがどうなったかわからない・・・。）

関連記事

公式ドキュメント
https://www.postgresql.jp/document/14/html/sql-createcollation.html

こちらの方のgistを参照させていただきました
https://gist.github.com/upinetree/9f643d02bdb8d245b66c

postgresのcollationについて概説
https://kiririmode.hatenablog.jp/entry/20230528/1685254374