Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Postgres PG::Error: ERROR: new encoding (UTF8) is incompatible
======= Prolbem =================================================================================================================
I have installed : ruby-2.0.0,postgres-9.2 , now in rails app when I execute:
rake db:create , command I get:
PG::InvalidParameterValue: 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 "my_db_name" ENCODING = 'unicode'.......
bin/rake:16:in `load'
bin/rake:16:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_db", "host"=>"localhost", "pool"=>5, "username" =>"my_user", "password"=>"my_password"}
=================================================================================================================================
Solution
=================================================================================================================================
Ok, below steps resolved the problem:
First, we need to drop template1. Templates can’t be dropped, so we first modify it so t’s an ordinary database:
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
Now we can drop it:
DROP DATABASE template1;
Now its time to create database from template0, with a new default encoding:
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
Now modify template1 so it’s actually a template:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
Now switch to template1 and VACUUM FREEZE the template:
\c template1
VACUUM FREEZE;
Problem should be resolved.

Awesome! This helped me solve an issue with default installation config issues with Puppet for a Vagrant dev environment box. Thanks for posting this!

Thanks man! this fix a tiny issue with: https://github.com/rails/rails-dev-box

Thanks bro :)

Thanks! This really helped out with an issue setting up a cloud9 workspace.

thogg4 commented Oct 7, 2014

Great solution. thanks.

Discovered this error can also be fixed in database.yml.

Add this line to database.yml for each db to specify the template:

template: template0

e.g.

development:
  adapter: postgresql
  host: localhost
  encoding: unicode
  database: appstore_development
  pool: 5
  username: someone
  password: some-password
  template: template0

test:
  adapter: postgresql
  host: localhost
  encoding: unicode
  database: appstore_test
  pool: 5
  username: someone
  password: some-password
  template: template0

Worked with Rails 4.1.x (with rails-dev-box postgres like @emaraschio)

chemica commented Dec 9, 2014

Awesome work, best solution I've found to this recurring issue!

Thanks!!!

Awesome! Thanks a lot!

Thank you a lot !

yevgenko commented Jan 7, 2016

👍

Efeinan commented Jan 20, 2016

@eliotsykes 's soulution is easier, thanks both.

kyptin commented Feb 5, 2016

If you do your own call to initdb, you can also specify the encoding with the -E option.

Thanks!
This helps me to use PostgreSQL on Cloud9!

Thanks!

rendon commented Mar 18, 2016

👍

Thanks! well explained.

thanks for the breakdown!

+1 freaking awesome.

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

This is perfect! thanks for posting it!

This works for me. Thanks for this.

Thank you very much

qqgwq commented Jan 3, 2017

thanks

ozanyurt commented Feb 3, 2017

thanks

Thanks! Fixed my issue also!

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

Thank you good sir!

p-pavlo commented Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment