j-_mia
Last Updated: February 25, 2016
·
18.18K
· 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.

Say Thanks
Respond

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 ·