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.
@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