Last Updated: October 26, 2022

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.

#postgresql

Written by katy lavallee

Say Thanks

Respond

Related protips

Hierarchical data in postgres

77.49K

Start and Stop PostgreSQL manually on Mac OS X

65.67K

Use IN instead of ANY in PostgreSQL

64.26K

2 Responses

Add your response

timbrandin

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 ·

gxela

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 ·