Rails 3.0.0 on PostgreSQL Not Listening to Character Encoding

It seemed rails wasn't listening to the character encoding I was setting in my database.yml for PostgreSQL connections. I was getting:

PGError: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT: Use the same encoding as in the template database, or use template0 as template.
: CREATE DATABASE "shop_test" ENCODING = 'utf8'

although I had 'sql_ascii' set as the encoding for this database. Curiously, at the end of the same error message, rake was complaining:

Couldn't create database for {"encoding"=>"sql_ascii", "port"=>5432, "username"=>"website", "adapter"=>"postgresql", "database"=>"shop_test", "host"=>"localhost", "pool"=>5, "password"=>"********"}

so obviously rake was listening to my setting in database.yml. But because the postgres error claimed we were trying to create a unicode database, it was clear the ball was being dropped somewhere within the rails domain.

Jeremy Wohl pointed me to a bug report from back in April that mentions this disconnect. This prompted me to change:

@encoding = config[:encoding] || ENV['CHARSET'] || 'utf8'

to

@encoding = config['encoding'] || ENV['CHARSET'] || 'utf8'

in

/usr/lib64/ruby/gems/1.8/gems/activerecord-3.0.0/lib/active_record/railties/databases.rake

which fixed it. Its curious why this fix hasn't yet made it to the release code as of October 6, 2010 given the fix is from back on April 1, 2010.

And for the record, I would normally use a unicode database in postgres but I can't easily figure out how to inherit from template0 "the rails way". The test database is the only one in sql_ascii though because that's the only one rails actually blows away and attempts to recreate. (and sql_ascii is the default encoding for this postgres instance (oversight from back when I set this instance up) and I have lots of other databases on this instance so changing the encoding on template1 wasn't practical) The other two databases (shop_development and shop_production) were made by hand and and don't get blown away by rails. As this is a test database, the only tests I can't do are ones related to unicode for the time being. I'm willing to live with it for now.

Comments (2)

Sam G from New Zealand

Create a utf8 template and set the template database to use in database.yml
development:
adapter: postgresql
template: template_utf8

Anders from Cambridge, MA

Thanks Sam, good suggestion.