How to make PostgreSQL use a UTF-8 template for CREATE DATABASE

October 11, 2012 PostgreSQL UTF-8

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

Buy Me a Coffee at ko-fi.com