PostgreSQLの照合順序をクエリ単位で指定する

環境

PostgreSQL12

文字セットを設定する

PostgreSQLで利用される文字セット（エンコーディング）は、initdbのタイミングで指定することができます。

特に指定しなければ、OSのlibcが提供しているロケールがデフォルでがセットされることになります。

たいてい、OSのロケールは、ja_JP.UTF-8（触っていなければen_US.UTF-8）がセットされているはずなので、PostgreSQLにもそのまま引き継がれているはずです。

また、createdbでデータベースを作成するときに、各データベース単位でロケールを指定することもできます。指定しなければ、initdbをしたときに設定した文字セットがそのまま引き継がれます。

データベース全体の文字セットを確認する方法です。

#SHOW LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8

データベース全体の文字セットはpg_settingsのシステムカタログに設定されているため、つぎのSQLで確認することもできます。

#select name, setting from pg_settings where name = 'lc_collate';
    name     |   setting   
-------------+-------------
 lc_collate  | en_US.UTF-8 

データベース単位でセットされている文字セットを確認する方法です。

>psql -l
                                         List of databases
          Name           |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------------------+----------+----------+-------------+-------------+-----------------------
 postgres                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 hoge_development        | vagrant  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 hoge_production         | vagrant  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 hoge_test               | vagrant  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
 template1               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
(6 rows)

文字セットの一覧

PostgreSQLで設定できる文字セットの一覧はつぎのSQLで確認することができます。このシステムカタログでセットされている値を使うことになります。

#select * from pg_collation;

icuロケールとは？

PostgreSQL10より組み込まれたicuロケールを利用したい場合は、利用するロケールを明示的に指定する必要があります。

icuとはInternational Components for Unicodeのことで、国際標準に対応したロケールのようです。これからのデファクトスタンダードとなるのだろうか？

ソートデータの中に、全角ひらがな、半角ひらがな、全角カタカナ、半角カタカナ、アルファベット、数字などが混在する場合、libcロケールとはソート順が微妙に異なってくるようです。

それにしても、あまりこだわる部分ではないような気はしますが、こんなところにこだわりが出てくるような業務はどのような業界なんだろう・・・

クエリ―単位で文字セットを指定

クエリ―単位で文字セットを指定することもできます。データベースの文字セットがen_US.utf8で設定されているが、アプリからjp_JP.utf8でソートさせたいような場合に、細工をかけることができます。

本番環境と開発環境とで文字セットが異なっている場合、日本語を扱っていると、微妙にソート順が異なるケースに出くわすことがあります。

そのような場合は、クエリー単位にカスタマイズをしてやり過ごすしかありません。Order By句で各カラムごとにCollateで指定してやると実現できます。

en_US.utf8の文字セットでソートさせる場合

Order By colum1 Collate "en_US.utf8"

jp_JP.utf8の文字セットでソートさせる場合

Order By colum1 Collate "jp_JP.utf8"

このようにカラムごとに混在させることもできます。（こんな使い方はないでしょうが・・・）

Order By colum1 Collate "jp_JP.utf8", colum1 Collate "en_US.utf8"