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?

willglynn · Accepted Answer · 2012-09-09 03:23:55Z

7

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.

answered Sep 9, 2012 at 3:23

willglynn

11.6k54 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Craig Ringer Over a year ago

Wow, that's really annoying. PostgreSQL supports setting per-database collations via CREATE DATABASE ... LC_CTYPE 'blah' LC_COLLATE 'blah'; it sucks that Heroku doesn't expose that to users.

Linus Over a year ago

Thank you, it seems to work to change the column collation. I did have to create the collation first though. Thanks.

Stefan Pettersson Over a year ago

Thanks! Finally. I have been struggling with this too long. For me it didn't work with "sv-SE.UTF-8" but "sv-SE" only. Using rails, I made a rake task to change the collation with following code: ---- namespace :setup do task :heroku_collation => :environment do ActiveRecord::Base.establish_connection ActiveRecord::Base.connection.execute 'ALTER TABLE municipalities ALTER COLUMN name TYPE varchar COLLATE "sv_SE";' end end

Marcus · Accepted Answer · 2014-09-04 10:31:49Z

1

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

answered Sep 4, 2014 at 10:31

Marcus

12.6k5 gold badges50 silver badges66 bronze badges

Collectives™ on Stack Overflow

Set locale on Heroku postgres

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related