CODEWITHSUNDEEP
postgresqlsortingunicode
sanichi
sanichi

Reputation: 415

Sort order in postgresql for japanese words in hiragana

I have a version 9.6.2 postgresql database with encoding UTF8 and collation en_GB.UTF8. One of my tables has a field containing Japanese Hiragana characters only. The problem is that sorting this field in the normal way through SQL (... ORDER BY [field name] ...) does not produce the expected (by me at least) results. Instead, the ordering is as if the rows are sorted by first by length (of the field contents) and only then by the characters. So for example, instead of getting:

あめ うえ おとこ かわり き し ひ

as I would expect, the order comes out as:

き し ひ あめ うえ おとこ かわり

What am I missing?

Upvotes: 3

Views: 1887

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51519

try setting collation C explicitly?

t=# with a as (select string_to_array('あめ うえ おとこ かわり き し ひ' COLLATE "en_GB.UTF-8",' ') a) select unnest(a) from a order by 1;
 unnest
--------
 き
 し
 ひ
 あめ
 うえ
 おとこ
 かわり
(7 rows)

t=# with a as (select string_to_array('あめ うえ おとこ かわり き し ひ' COLLATE "C",' ') a) select unnest(a) from a order by 1;
 unnest
--------
 あめ
 うえ
 おとこ
 かわり
 き
 し
 ひ
(7 rows)

Upvotes: 2

Related Questions