Introduction

Changing the encoding of the template databases to UTF-8 is a common issue with PostgreSQL. Postgresql uses an encoding for each database. If your application uses Unicode, you could have Unicode errors when you commit to the database.

You can use a query to show the encoding of the database:

sudo -u postgres psql -c "SHOW SERVER_ENCODING" your_database

If the output displays "SQL_ASCII", then you could be at trouble.

You can create the database using utf8 with:

sudo -u postgres createdb -E utf8 your_database

However, there are big chances that you will get the following error when you try to create the database with utf8 encoding:

try to create a DB with Unicode encoding the following message is returned to me :

new encoding (UTF8) is incompatible with the encoding of the
template database (SQL_ASCII)

In this tutorial, we are going to explain how to change the database encoding to avoid Unicode error with INSERT and UPDATE queries.

WARNING: Make sure you backup everything, the next steps could destroy data!

Step1: Backup

pg_dump your_database > backup_file

Now you have to delete the database

sudo -u postgres dropdb your_database

Step 2: Changing the template encoding

At this step we are going to drop template1 (the default template database) and recreate it from template0 with a utf-8 encoding. Remember you can use other encodings:

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

Postgres cluster step (optional step for clusters)

When using PostgreSQL cluster, you may wish to drop and reinitialize your entire cluster. You can change the encoding with:

sudo pg_dropcluster --stop 9.2 main
sudo pg_createcluster --locale=en_US.utf8 --start 9.2 main

Step 3: Create the database with new encoding

createdb -E utf8 your_database

Step 4: Restore backup

psql your_database < backup_file

Appendix

Ruby rails error related to database encoding

When you try to execute:

rake db:create

You get the error:

PG::Error: 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 "verticals_test" ENCODING = 'unicode'
/home/tutorials/rails_example/.gems/ruby/1.9.1/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:652:in `exec
....
bin/rake:16:in `load'
bin/rake:16:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"project_db_name", "host"=>"localhost", "pool"=>5, "username"=>"db_user", "password"=>"secret"}