🤖🚫 AI-free content. This post is 100% written by a human, as is everything on my blog. Enjoy!

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

October 11, 2012 in SQL

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 Liked the post? Treat me to a coffee