How to make PostgreSQL use a UTF-8 template for CREATE DATABASE
October 11, 2012 in SQLSave this script as psql_utf8_by_default.sql, and run psql < psql_utf8_by_default.sql, or rails dbconsole < psql_utf8_by_default.sql
update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0;
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;
update pg_database set datallowconn = FALSE where datname = 'template0';
Done! Now rake db:create and rake db:migrate:reset works properly.
Liked the post? Treat me to a coffee