Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save amolkhanorkar/8706915 to your computer and use it in GitHub Desktop.
Save amolkhanorkar/8706915 to your computer and use it in GitHub Desktop.
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


Copy link

rendon commented Mar 18, 2016


Copy link

Thanks! well explained.

Copy link

Copy link

maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

Copy link

thanks for the breakdown!

Copy link

+1 freaking awesome.

Copy link

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

Copy link

This is perfect! thanks for posting it!

Copy link

This works for me. Thanks for this.

Copy link

Thank you very much

Copy link

qqgwq commented Jan 3, 2017


Copy link

ozanyurt commented Feb 3, 2017


Copy link

Thanks! Fixed my issue also!

Copy link

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

Copy link

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

Thank you, It worked for me

Copy link

Ex-Ark commented Jul 11, 2018

thanks, it worked nicely

Copy link

@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

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

Thank you! It worked for me.

Copy link

faust64 commented Sep 8, 2020

Awesome. Thanks a lot.

Copy link

lol 2020 and work ! Awesome, thank you men.

Copy link


Copy link

awesome, it's working!

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