Postgres PG::Error: ERROR: new encoding (UTF8) is incompatible

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.

Thanks!

Thank you, It worked for me

thanks, it worked nicely

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

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

Perfect solution! Thank you :)

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;

Thank you! It worked for me.

Awesome. Thanks a lot.

lol 2020 and work ! Awesome, thank you men.

Tanks

awesome, it's working!

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;