Last Updated: October 26, 2022
·
34.76K
· katylava

Make postgres default to UTF8

UPDATE pg_database SET datistemplate=FALSE WHERE datname='template1';

DROP DATABASE template1;

CREATE DATABASE template1 WITH owner=postgres template=template0 encoding='UTF8';

UPDATE pg_database SET datistemplate=TRUE WHERE datname='template1';

At DjangoCon 2012 there was a speaker with ~20 years experience with PostgreSQL who said not to do this. The reason was you couldn't be sure all the data in your database currently was compatible with UTF8. However I think we were talking about different things. This tip does not affect existing databases, only any new databases you create afterwards. Don't be scared. It works and it's safe.

2 Responses
Add your response

One also have to have UTF8 as default in the locale settings for the postgres user in unix.

export LANGUAGE="en_US.UTF-8"
export LANG="en_US.UTF-8"
export LC_ALL="en_US.UTF-8"

This solved the puzzle for me.

over 1 year ago ·
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template0';
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template1';
over 1 year ago ·