Skip to content

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"}
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:
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
Problem should be resolved.
Copy link

miminashi commented Mar 6, 2016

This helps me to use PostgreSQL on Cloud9!

Copy link

dminchev commented Mar 18, 2016


Copy link

rendon commented Mar 18, 2016


Copy link

AkashChandrakar commented Mar 22, 2016

Thanks! well explained.

Copy link

murtuzamvista commented Apr 14, 2016

Copy link

maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

Copy link

mcardacci commented Jun 15, 2016

thanks for the breakdown!

Copy link

holtalanm commented Jul 22, 2016

+1 freaking awesome.

Copy link

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

Copy link

mandym-webdev commented Sep 10, 2016

This is perfect! thanks for posting it!

Copy link

pdagrawal commented Nov 14, 2016

This works for me. Thanks for this.

Copy link

tonyvince commented Dec 15, 2016

Thank you very much

Copy link

qqgwq commented Jan 3, 2017


Copy link

ozanyurt commented Feb 3, 2017


Copy link

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

Thanks! Fixed my issue also!

Copy link

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

Copy link

TheWolfCoder commented Mar 12, 2017

Thank you good sir!

Copy link

p-pavlo commented Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

Copy link

mshegolev commented Dec 7, 2017

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

Copy link

dimoha commented Feb 9, 2018


Copy link

PebaneThato commented Apr 10, 2018

Thank you, It worked for me

Copy link

Ex-Ark commented Jul 11, 2018

thanks, it worked nicely

Copy link

jpfinlay commented Jul 20, 2018

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

Copy link

jyeshe commented Jul 8, 2019

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

Copy link

Rey810 commented Jan 31, 2020

Perfect solution! Thank you :)

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;

Copy link

davidlescanoar commented Jun 19, 2020

Thank you! It worked for me.

Copy link

faust64 commented Sep 8, 2020

Awesome. Thanks a lot.

Copy link

AnonymousWebHacker commented Nov 13, 2020

lol 2020 and work ! Awesome, thank you men.

Copy link

WallasFaria commented Jun 22, 2022


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