How to change postgresql database encoding to UTF8 from SQL_ASCII

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.

PosgreSQL Logo

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"}