Set locale on postgres Heroku

Question

I am using the base database plan in Heroku. This works on Postgres 9.1, which supports local area networks. I have problems sorting in my application because ÅÄÖ characters are not being processed properly (as in Sweden).

The set LC_COLLATE parameter, which handles the row order. The problem is that I cannot find a way to install this on Heroku. The created databases get lc_collate=en_US.UTF-8 , but I need to set it to sv_SE.UTF-8 .

This LC_COLLATE parameter cannot be changed when the database was created, so I cannot change it through the psql console.

So how can I install this?

Answer 1

You are correct that you cannot change the default setting for the database; LC_COLLATE is an environment variable set on Heroku database servers that is out of your control and already set before the database was created. However, you can set the default mapping for individual columns:

 CREATE TABLE new_table ( foo varchar COLLATE "sv_SE.UTF-8", bar varchar COLLATE "sv_SE.UTF-8" ); ALTER TABLE existing_table ALTER COLUMN baz TYPE varchar COLLATE "sv_SE.UTF-8"; 

See details in 22.2. Sorting support in the PostgreSQL manual.

You may need CREATE COLLATION . In addition, it all depends on Heroku database servers that have the correct locale data, although if they do not, you could probably ask for it to be deployed as it will not hurt anyone.

Otherwise, you could run your own PostgreSQL instances in EC2 with whatever custom setting you want. It will take investment administration time, but honestly working 9.1 is pretty simple, even including in-line replication. Perhaps even cheaper. The downside is that keeping the database running becomes your problem, not a problem for the Herocu ops team.

Answer 2

I solved this by creating a migration that changes the sorting of the corresponding columns (as the prompt suggests):

 class SetSwedishCollationForStores < ActiveRecord::Migration def up execute 'ALTER TABLE stores ALTER COLUMN city TYPE varchar COLLATE "sv_SE";' execute 'ALTER TABLE stores ALTER COLUMN title TYPE varchar COLLATE "sv_SE";' end end