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"}
=================================================================================================================================
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.
@maneto
Copy link

maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

@mcardacci
Copy link

thanks for the breakdown!

@holtalanm
Copy link

+1 freaking awesome.

@akshaybharwani
Copy link

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

@mandym-webdev
Copy link

This is perfect! thanks for posting it!

@pdagrawal
Copy link

This works for me. Thanks for this.

@tonyvince
Copy link

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

Thanks! Fixed my issue also!

@Bovojon
Copy link

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

@theshirokumacoder
Copy link

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

Thank you, It worked for me

@Ex-Ark
Copy link

Ex-Ark commented Jul 11, 2018

thanks, it worked nicely

@jpfinlay
Copy link

@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

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

Thank you! It worked for me.

@faust64
Copy link

faust64 commented Sep 8, 2020

Awesome. Thanks a lot.

@AnonymousWebHacker
Copy link

lol 2020 and work ! Awesome, thank you men.

@WallasFaria
Copy link

Tanks

@deep-explorer
Copy link

awesome, it's working!

@tadghh
Copy link

tadghh commented Aug 30, 2024

for le lazy

        UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
        DROP DATABASE template1;
        CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
        UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
        \c template1
        VACUUM FREEZE;

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