j-_mia
Last Updated: February 25, 2016
·
17.75K
· katylava
20bc23f99b21af3f2a34095799adea63

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

9593
4ac7c0b696ba968d2353ba74ead0a899

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 ·
14184
9ea742cae8e2d5b6239783d920dfcc67
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 ·