Set locale on Heroku postgres

Question

I'm using the Basic database plan at Heroku. This runs on Postgres 9.1 which has support for locales. I'm having issues with sorting in my app because the characters ÅÄÖ is not treated properly (as they should in Sweden).

The setting to set is LC_COLLATE, which handles string ordering. The problem is that I can't find any way to set this on Heroku. The databases that are created get lc_collate=en_US.UTF-8, but I need to set it to sv_SE.UTF-8.

This LC_COLLATE setting can't be changed when the database has been created, hence I can't change it through the psql console.

So, how can I set this?

Answer 1

You're correct that can't change the database's default collation; LC_COLLATE is an environment variable set on the Heroku database servers, which is both outside your control and already set before your database was created. You can, however, set the default collation 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";

For more, see 22.2. Collation Support in the PostgreSQL manual.

You may or may not need to CREATE COLLATION first. Additionally, all this depends on the Heroku database servers having the proper locale data installed -- although if they don't, you could probably ask nicely to get that deployed, since it wouldn't hurt anyone.

Failing that, you could of course run your own PostgreSQL instances in EC2, with whatever custom setup you want. That would require investing administration time, but honestly running 9.1 is pretty straightforward, even including streaming replication. Might even be cheaper too. The downside there is that keeping the database running becomes your problem instead of a problem for the Heroku ops team.

Answer 2

I solved this by creating a migration that alters the collation of the concerned columns (like willglyn 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