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.
@miminashi
Copy link

miminashi commented Mar 6, 2016

Thanks!
This helps me to use PostgreSQL on Cloud9!

@dminchev
Copy link

dminchev commented Mar 18, 2016

Thanks!

@rendon
Copy link

rendon commented Mar 18, 2016

👍

@AkashChandrakar
Copy link

AkashChandrakar commented Mar 22, 2016

Thanks! well explained.

@murtuzamvista
Copy link

murtuzamvista commented Apr 14, 2016

@maneto
Copy link

maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

@mcardacci
Copy link

mcardacci commented Jun 15, 2016

thanks for the breakdown!

@holtalanm
Copy link

holtalanm commented Jul 22, 2016

+1 freaking awesome.

@akshaybharwani
Copy link

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

@mandym-webdev
Copy link

mandym-webdev commented Sep 10, 2016

This is perfect! thanks for posting it!

@pdagrawal
Copy link

pdagrawal commented Nov 14, 2016

This works for me. Thanks for this.

@tonyvince
Copy link

tonyvince commented Dec 15, 2016

Thank you very much

@qqgwq
Copy link

qqgwq commented Jan 3, 2017

thanks

@ozanyurt
Copy link

ozanyurt commented Feb 3, 2017

thanks

@Random-Stack-Random-Day
Copy link

Random-Stack-Random-Day commented Mar 4, 2017

Thanks! Fixed my issue also!

@Bovojon
Copy link

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

@TheWolfCoder
Copy link

TheWolfCoder commented Mar 12, 2017

Thank you good sir!

@p-pavlo
Copy link

p-pavlo commented Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

@mshegolev
Copy link

mshegolev commented Dec 7, 2017

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

@dimoha
Copy link

dimoha commented Feb 9, 2018

Thanks!

@PebaneThato
Copy link

PebaneThato commented Apr 10, 2018

Thank you, It worked for me

@Ex-Ark
Copy link

Ex-Ark commented Jul 11, 2018

thanks, it worked nicely

@jpfinlay
Copy link

jpfinlay commented Jul 20, 2018

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

@jyeshe
Copy link

jyeshe commented Jul 8, 2019

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

@Rey810
Copy link

Rey810 commented Jan 31, 2020

Perfect solution! Thank you :)

@mimie-chan
Copy link

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
Copy link

davidlescanoar commented Jun 19, 2020

Thank you! It worked for me.

@faust64
Copy link

faust64 commented Sep 8, 2020

Awesome. Thanks a lot.

@AnonymousWebHacker
Copy link

AnonymousWebHacker commented Nov 13, 2020

lol 2020 and work ! Awesome, thank you men.

@WallasFaria
Copy link

WallasFaria commented Jun 22, 2022

Tanks

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