1

I am trying to setup my rails app to use latin1 encoding using the following database.yml

development:
adapter: postgresql
database: pana_development
pool: 5
timeout: 5000
encoding: latin1

When running rake db:create I get the following error:

Couldn't create database for {"adapter"=>"postgresql", "database"=>"pana_development",    "pool"=>5, "timeout"=>5000, "encoding"=>"latin1"}
PG::Error: ERROR:  encoding LATIN1 does not match locale en_US.UTF-8
DETAIL:  The chosen LC_CTYPE setting requires encoding UTF8.

What do I need to do in order to setup rails using latin1 encoding in a postgresql database?

Baconator507
  • 1,747
  • 2
  • 12
  • 20

2 Answers2

3

There is a script which I have found in Gist which worked like a charm

sudo su postgres

psql

update pg_database set datistemplate=false where datname='template1';
drop database Template1;
create database template1 with owner=postgres encoding='UTF-8'
  lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;

update pg_database set datistemplate=true where datname='template1';

You can get the original source here

Sri Harsha Kappala
  • 3,339
  • 26
  • 23
  • 30
  • doesn’t work for me. results in postgres=# create database template1 with owner=postgres encoding='UTF-8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0; ERROR: invalid locale name: "en_US.utf8" and now my Template1 database is dropped. – Uilleann Jan 13 '17 at 16:22
1

First, you need to supply the program versions.
Next, you need to understand that the default locale setting LC_CTYPE is determined at cluster creation time. I quote the manual here:

Some locale categories must have their values fixed when the database is created. You can use different settings for different databases, but once a database is created, you cannot change them for that database anymore. LC_COLLATE and LC_CTYPE are these categories. They affect the sort order of indexes, so they must be kept fixed, or indexes on text columns would become corrupt. (But you can alleviate this restriction using collations, as discussed in Section 22.2.) The default values for these categories are determined when initdb is run, and those values are used when new databases are created, unless specified otherwise in the CREATE DATABASE command.

You can easily solve this with CREATE DATABASE building on the template0 template database, which has no pre-determined objects in it.

CREATE DATABASE pana_development ENCODING 'LATIN1' TEMPLATE template0;

In Ruby, you can just supply a "template" parameter:

template: template0

Compare this closely related answer:
rake db:create encoding error with postgresql

For repeated use you should prepare a database like outlined above, put everything in it that you want in future databases and use this one as template. Any database can be used as template.

Or you create a new database cluster with matching LC_TYPE and take it from there.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the quick answer. I tried runnig that psql command but it gives me the same error. I checked and I have no template0 db. – Baconator507 Jul 02 '13 at 15:58
  • @Baconator507: Unless you are using a custom package or have tampered with the system you should have a template0 database. It's created by default. Try `\l` in psql. Either way, please supply your version of Postgres *in your question* before asking anything else. – Erwin Brandstetter Jul 02 '13 at 16:01
  • @Erin, you are correct, I DO have the template0. However, I noticed that it is also on en_US.UTF-8. – Baconator507 Jul 02 '13 at 16:04
  • @Baconator507: That is not a problem. Read the manual. I supplied deep links. – Erwin Brandstetter Jul 02 '13 at 16:04
  • @Erin: It looks like I need to create a new cluster that uses LATIN1, is this what you suggest? – Baconator507 Jul 02 '13 at 16:24