山ｐの楽しいお勉強生活

勉強の成果を垂れ流していきます

PostgreSQLのデフォルトLOCALEでは日本語文字列のソート結果が想定と異なる

PostgreSQL

概要

  • PostgreSQLをデフォルトのまま使用すると日本語文字列のソート結果が想定と異なる
  • LOCALEを正しく設定することで修正可能
  • 文字列の並び順は LC_COLLATE で制御されるこの設定がデフォルトだと en_US.utf8 となっていることが原因

動作確認環境

対応方法

FROM postgres:11.5
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
ENV TZ=Asia/Tokyo
ENV LANG=ja_JP.UTF-8
ENV LANGUAGE=ja_JP:ja
ENV LC_ALL=ja_JP.UTF-8

詳細及び再現手順

再現

起動

docker run -d --name postgres_1 postgres:11.5
docker exec -it postgres_1 psql -U postgres

SQL

create table hoge ( id int, value varchar(10));
insert into hoge values
(1, 'あ'),
(2, 'あ（ほげ）'),
(3, 'い'),
(4, 'い（ふが）')
;
select * from hoge order by value;

結果

postgres=# select * from hoge order by value;
 id |   value    
----+------------
  1 | あ
  3 | い
  2 | あ（ほげ）
  4 | い（ふが）
(4 rows)

LOCALEの確認

postgres=# SHOW LC_COLLATE;
 lc_collate 
------------
 en_US.utf8
(1 row)

対応

Dockerfile

FROM postgres:11.5
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
ENV TZ=Asia/Tokyo
ENV LANG=ja_JP.UTF-8
ENV LANGUAGE=ja_JP:ja
ENV LC_ALL=ja_JP.UTF-8

起動

docker build . -t yamap55/postgres:11.5_jajp
docker run -d --name postgres_2 yamap55/postgres:11.5_jajp
docker exec -it postgres_2 psql -U postgres

SQL

create table hoge ( id int, value varchar(10));
insert into hoge values
(1, 'あ'),
(2, 'あ（ほげ）'),
(3, 'い'),
(4, 'い（ふが）')
;
select * from hoge order by value;

結果

postgres=# select * from hoge order by value;
 id |   value    
----+------------
  1 | あ
  2 | あ（ほげ）
  3 | い
  4 | い（ふが）
(4 行)

LOCALEの確認

postgres=# SHOW LC_COLLATE;
 lc_collate  
-------------
 ja_JP.UTF-8
(1 行)

参考

背景とか蛇足とか

MySQLでも試した

起動、接続

docker run --name mysql_1 -e MYSQL_ROOT_PASSWORD=mysql -d mysql
docker exec -it mysql_1 mysql -u root -p -h 127.0.0.1 -D mysql -pmysql

結果

mysql> create table hoge ( id int, value varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into hoge values
    -> (1, ''),
    -> (2, ''),
    -> (3, ''),
    -> (4, '')
    -> ;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from hoge;
+------+-------+
| id   | value |
+------+-------+
|    1 |       |
|    2 |       |
|    3 |       |
|    4 |       |
+------+-------+
4 rows in set (0.00 sec)

mysql>