How to change a query result by locale on Heroku Postgres?

Issue

I want to get a query result by locale, but it appears that LC_COLLATE cannot be changed.

Resolution

This is an example of changing en_US.UTF-8 (default) to ja_JP.utf8:

Create the collation with

CREATE COLLATION ja_JP (locale = 'ja_JP.utf8');

And then execute the following query:

ALTER TABLE table_name ALTER COLUMN column_name TYPE VARCHAR COLLATE ja_JP;

Please refer to https://www.postgresql.org/docs/current/collation.html for more details.