Skip to content

Instantly share code, notes, and snippets.

Embed
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.

Copy link

@yonkeltron yonkeltron commented 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!

@emaraschio

This comment has been minimized.

Copy link

@emaraschio emaraschio commented Aug 6, 2014

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

@engineer

This comment has been minimized.

Copy link

@engineer engineer commented Aug 17, 2014

Thanks bro :)

@sethyanow

This comment has been minimized.

Copy link

@sethyanow sethyanow commented Sep 17, 2014

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

@thogg4

This comment has been minimized.

Copy link

@thogg4 thogg4 commented Oct 7, 2014

Great solution. thanks.

@eliotsykes

This comment has been minimized.

Copy link

@eliotsykes eliotsykes commented 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)

@frankie-loves-jesus

This comment has been minimized.

Copy link

@frankie-loves-jesus frankie-loves-jesus commented Oct 17, 2014

Nice !!

@chemica

This comment has been minimized.

Copy link

@chemica chemica commented Dec 9, 2014

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

@scientist1642

This comment has been minimized.

Copy link

@scientist1642 scientist1642 commented Apr 21, 2015

thank you @eliotsykes

@rkcitrus

This comment has been minimized.

Copy link

@rkcitrus rkcitrus commented Jun 11, 2015

@dcousette

This comment has been minimized.

Copy link

@dcousette dcousette commented Jun 15, 2015

Thanks!!!

@zhrivodkin

This comment has been minimized.

Copy link

@zhrivodkin zhrivodkin commented Jun 24, 2015

Awesome! Thanks a lot!

@zzeni

This comment has been minimized.

Copy link

@zzeni zzeni commented Sep 30, 2015

@kalilz4485

This comment has been minimized.

Copy link

@kalilz4485 kalilz4485 commented Dec 15, 2015

Thank you a lot !

@yevgenko

This comment has been minimized.

Copy link

@yevgenko yevgenko commented Jan 7, 2016

👍

@Efeinan

This comment has been minimized.

Copy link

@Efeinan Efeinan commented Jan 20, 2016

@eliotsykes 's soulution is easier, thanks both.

@kyptin

This comment has been minimized.

Copy link

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

Copy link

@miminashi miminashi commented Mar 6, 2016

Thanks!
This helps me to use PostgreSQL on Cloud9!

@dminchev

This comment has been minimized.

Copy link

@dminchev dminchev commented Mar 18, 2016

Thanks!

@rendon

This comment has been minimized.

Copy link

@rendon rendon commented Mar 18, 2016

👍

@AkashChandrakar

This comment has been minimized.

Copy link

@AkashChandrakar AkashChandrakar commented Mar 22, 2016

Thanks! well explained.

@murtuzamvista

This comment has been minimized.

Copy link

@murtuzamvista murtuzamvista commented Apr 14, 2016

@maneto

This comment has been minimized.

Copy link

@maneto maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

@mcardacci

This comment has been minimized.

Copy link

@mcardacci mcardacci commented Jun 15, 2016

thanks for the breakdown!

@holtalanm

This comment has been minimized.

Copy link

@holtalanm holtalanm commented Jul 22, 2016

+1 freaking awesome.

@akshaybharwani

This comment has been minimized.

Copy link

@akshaybharwani akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

@mandym-webdev

This comment has been minimized.

Copy link

@mandym-webdev mandym-webdev commented Sep 10, 2016

This is perfect! thanks for posting it!

@pdagrawal

This comment has been minimized.

Copy link

@pdagrawal pdagrawal commented Nov 14, 2016

This works for me. Thanks for this.

@tonyvince

This comment has been minimized.

Copy link

@tonyvince tonyvince commented Dec 15, 2016

Thank you very much

@qqgwq

This comment has been minimized.

Copy link

@qqgwq qqgwq commented Jan 3, 2017

thanks

@ozanyurt

This comment has been minimized.

Copy link

@ozanyurt ozanyurt commented Feb 3, 2017

thanks

@dnorthrupva

This comment has been minimized.

Copy link

@dnorthrupva dnorthrupva commented Mar 4, 2017

Thanks! Fixed my issue also!

@Bovojon

This comment has been minimized.

Copy link

@Bovojon Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

@TheWolfCoder

This comment has been minimized.

Copy link

@TheWolfCoder TheWolfCoder commented Mar 12, 2017

Thank you good sir!

@p-pavlo

This comment has been minimized.

Copy link

@p-pavlo p-pavlo commented Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

@mshegolev

This comment has been minimized.

Copy link

@mshegolev mshegolev commented Dec 7, 2017

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

@dimoha

This comment has been minimized.

Copy link

@dimoha dimoha commented Feb 9, 2018

Thanks!

@PebaneThato

This comment has been minimized.

Copy link

@PebaneThato PebaneThato commented Apr 10, 2018

Thank you, It worked for me

@Ex-Ark

This comment has been minimized.

Copy link

@Ex-Ark Ex-Ark commented Jul 11, 2018

thanks, it worked nicely

@jpfinlay

This comment has been minimized.

Copy link

@jpfinlay jpfinlay commented Jul 20, 2018

@amolkhanorkar -- thank you! This solved a big headache for me! 👍

@jyeshe

This comment has been minimized.

Copy link

@jyeshe jyeshe commented Jul 8, 2019

UPDATE pg_database SET datistemplate = true, encoding = 6 WHERE datname = 'template1';
works for me

@Rey810

This comment has been minimized.

Copy link

@Rey810 Rey810 commented Jan 31, 2020

Perfect solution! Thank you :)

@mimie-chan

This comment has been minimized.

Copy link

@mimie-chan mimie-chan commented May 25, 2020

In my case, I drop the template1 database before. I thought it was not needed.
Then I got a following error:

PG::InvalidCatalogName: ERROR: template database "template1" does not exist

I just create a copy of a database, run the following command in psql:

CREATE DATABASE [Database to create] WITH TEMPLATE [Database to copy] OWNER [Your username];

CREATE DATABASE template1 WITH TEMPLATE postgres OWNER postgres;

@davidlescanoar

This comment has been minimized.

Copy link

@davidlescanoar davidlescanoar commented Jun 19, 2020

Thank you! It worked for me.

@faust64

This comment has been minimized.

Copy link

@faust64 faust64 commented Sep 8, 2020

Awesome. Thanks a lot.

@AnonymousWebHacker

This comment has been minimized.

Copy link

@AnonymousWebHacker AnonymousWebHacker commented Nov 13, 2020

lol 2020 and work ! Awesome, thank you men.

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