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! well explained.

@eliotsykes +1

thanks for @amolkhanorkar and @eliotsykes

thanks for the breakdown!

+1 freaking awesome.

The real MVP right here people! Thank you!

This is perfect! thanks for posting it!

This works for me. Thanks for this.

Thank you very much

thanks

thanks

Thanks! Fixed my issue also!

Thank you! Worked like a charm!

Thank you good sir!

Thanks a lot! Now it works perfectly with cloud9

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

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;