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.
@yonkeltron

This comment has been minimized.

Show comment Hide comment
@yonkeltron

yonkeltron Feb 27, 2014

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

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

@emaraschio

This comment has been minimized.

Show comment Hide comment
@emaraschio

emaraschio Aug 6, 2014

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

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

@engineer

This comment has been minimized.

Show comment Hide comment
@engineer

engineer Aug 17, 2014

Thanks bro :)

Thanks bro :)

@sethyanow

This comment has been minimized.

Show comment Hide comment
@sethyanow

sethyanow Sep 17, 2014

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

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

@thogg4

This comment has been minimized.

Show comment Hide comment
@thogg4

thogg4 Oct 7, 2014

Great solution. thanks.

thogg4 commented Oct 7, 2014

Great solution. thanks.

@eliotsykes

This comment has been minimized.

Show comment Hide comment
@eliotsykes

eliotsykes Oct 13, 2014

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)

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)

@frankie-loves-jesus

This comment has been minimized.

Show comment Hide comment
@frankie-loves-jesus

frankie-loves-jesus Oct 17, 2014

Nice !!

Nice !!

@chemica

This comment has been minimized.

Show comment Hide comment
@chemica

chemica Dec 9, 2014

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

chemica commented Dec 9, 2014

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

@scientist1642

This comment has been minimized.

Show comment Hide comment
@scientist1642

scientist1642 Apr 21, 2015

thank you @eliotsykes

thank you @eliotsykes

@rkcitrus

This comment has been minimized.

Show comment Hide comment
@rkcitrus

rkcitrus Jun 11, 2015

@dcousette

This comment has been minimized.

Show comment Hide comment
@dcousette

dcousette Jun 15, 2015

Thanks!!!

Thanks!!!

@zhrivodkin

This comment has been minimized.

Show comment Hide comment
@zhrivodkin

zhrivodkin Jun 24, 2015

Awesome! Thanks a lot!

Awesome! Thanks a lot!

@zzeni

This comment has been minimized.

Show comment Hide comment
@zzeni

zzeni Sep 30, 2015

@kalilz4485

This comment has been minimized.

Show comment Hide comment
@kalilz4485

kalilz4485 Dec 15, 2015

Thank you a lot !

Thank you a lot !

@yevgenko

This comment has been minimized.

Show comment Hide comment
@yevgenko

yevgenko Jan 7, 2016

👍

yevgenko commented Jan 7, 2016

👍

@Efeinan

This comment has been minimized.

Show comment Hide comment
@Efeinan

Efeinan Jan 20, 2016

@eliotsykes 's soulution is easier, thanks both.

Efeinan commented Jan 20, 2016

@eliotsykes 's soulution is easier, thanks both.

@kyptin

This comment has been minimized.

Show comment Hide comment
@kyptin

kyptin Feb 5, 2016

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

kyptin commented Feb 5, 2016

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

@miminashi

This comment has been minimized.

Show comment Hide comment
@miminashi

miminashi Mar 6, 2016

Thanks!
This helps me to use PostgreSQL on Cloud9!

Thanks!
This helps me to use PostgreSQL on Cloud9!

@dminchev

This comment has been minimized.

Show comment Hide comment
@dminchev

dminchev Mar 18, 2016

Thanks!

Thanks!

@rendon

This comment has been minimized.

Show comment Hide comment
@rendon

rendon Mar 18, 2016

👍

rendon commented Mar 18, 2016

👍

@AkashChandrakar

This comment has been minimized.

Show comment Hide comment
@AkashChandrakar

AkashChandrakar Mar 22, 2016

Thanks! well explained.

Thanks! well explained.

@murtuzamvista

This comment has been minimized.

Show comment Hide comment
@murtuzamvista

murtuzamvista Apr 14, 2016

@maneto

This comment has been minimized.

Show comment Hide comment
@maneto

maneto May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

@mcardacci

This comment has been minimized.

Show comment Hide comment
@mcardacci

mcardacci Jun 15, 2016

thanks for the breakdown!

thanks for the breakdown!

@holtalanm

This comment has been minimized.

Show comment Hide comment
@holtalanm

holtalanm Jul 22, 2016

+1 freaking awesome.

+1 freaking awesome.

@akshaybharwani

This comment has been minimized.

Show comment Hide comment
@akshaybharwani

akshaybharwani Jul 26, 2016

The real MVP right here people! Thank you!

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

@mandym-webdev

This comment has been minimized.

Show comment Hide comment
@mandym-webdev

mandym-webdev Sep 10, 2016

This is perfect! thanks for posting it!

This is perfect! thanks for posting it!

@pdagrawal

This comment has been minimized.

Show comment Hide comment
@pdagrawal

pdagrawal Nov 14, 2016

This works for me. Thanks for this.

This works for me. Thanks for this.

@tonyvince

This comment has been minimized.

Show comment Hide comment
@tonyvince

tonyvince Dec 15, 2016

Thank you very much

Thank you very much

@qqgwq

This comment has been minimized.

Show comment Hide comment
@qqgwq

qqgwq Jan 3, 2017

thanks

qqgwq commented Jan 3, 2017

thanks

@ozanyurt

This comment has been minimized.

Show comment Hide comment
@ozanyurt

ozanyurt Feb 3, 2017

thanks

ozanyurt commented Feb 3, 2017

thanks

@dnorthrupva

This comment has been minimized.

Show comment Hide comment
@dnorthrupva

dnorthrupva Mar 4, 2017

Thanks! Fixed my issue also!

Thanks! Fixed my issue also!

@Bovojon

This comment has been minimized.

Show comment Hide comment
@Bovojon

Bovojon Mar 5, 2017

Thank you! Worked like a charm!

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

@TheWolfCoder

This comment has been minimized.

Show comment Hide comment
@TheWolfCoder

TheWolfCoder Mar 12, 2017

Thank you good sir!

Thank you good sir!

@p-pavlo

This comment has been minimized.

Show comment Hide comment
@p-pavlo

p-pavlo Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

p-pavlo commented Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

@mshegolev

This comment has been minimized.

Show comment Hide comment
@mshegolev

mshegolev Dec 7, 2017

That's works, thank you. I changed ascii to utf-8.

mshegolev commented Dec 7, 2017

That's works, thank you. I changed ascii to utf-8.

@dimoha

This comment has been minimized.

Show comment Hide comment
@dimoha

dimoha Feb 9, 2018

Thanks!

dimoha commented Feb 9, 2018

Thanks!

@PebaneThato

This comment has been minimized.

Show comment Hide comment
@PebaneThato

PebaneThato Apr 10, 2018

Thank you, It worked for me

Thank you, It worked for me

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