Instantly share code, notes, and snippets.

Embed
What would you like to do?
Change postgres default template0 to UTF8 encoding
mike@rbci:~$ psql -U postgres
psql (9.0.3)
Type "help" for help.
postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';
UPDATE 1
postgres=# \c template0
You are now connected to database "template0".
template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
UPDATE 1
template0=# drop database template1;
DROP DATABASE
template0=# create database template1 with template = template0 encoding = 'UTF8';
CREATE DATABASE
template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
UPDATE 1
template0=# \c template1
You are now connected to database "template1".
template1=# update pg_database set datallowconn = FALSE where datname = 'template0';
UPDATE 1
@rietta

This comment has been minimized.

rietta commented Feb 20, 2013

Thank you! Worked like a charm. Perfect timing posting this gist too.

@fgrehm

This comment has been minimized.

fgrehm commented Apr 22, 2013

@mrahma01

This comment has been minimized.

mrahma01 commented May 20, 2013

Line 13 works for me with the following way, failed otherwise, can't recognise UTF8
"create database template1 with ENCODING = 'UTF-8' LC_CTYPE = 'en_US.utf8' LC_COLLATE = 'en_US.utf8' template = template0;"

@AJ-Acevedo

This comment has been minimized.

AJ-Acevedo commented Jul 25, 2013

This was really helpful! Thanks!

@darkaico

This comment has been minimized.

darkaico commented Jul 30, 2013

Works perfectly! thanks!!

@tedstrauss

This comment has been minimized.

tedstrauss commented Aug 21, 2013

After going through the steps, I have template1 with UTF8, not template0...?

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | LATIN1   | en_US      | en_US      |
 template0 | postgres | LATIN1   | en_US      | en_US      | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
@rogeralmeida

This comment has been minimized.

rogeralmeida commented Oct 7, 2013

Dude, God bless you! :D

@omarish

This comment has been minimized.

omarish commented Jan 16, 2014

👍

@soufianeEL

This comment has been minimized.

soufianeEL commented Apr 15, 2014

very helpful .. 👍

@dusdanig

This comment has been minimized.

dusdanig commented Apr 16, 2014

The encoding and locale can also be specified at database initialization: http://www.postgresql.org/docs/9.3/static/app-initdb.html

@Depado

This comment has been minimized.

Depado commented Jun 4, 2014

Thanks you're amazing ;)

@mraaroncruz

This comment has been minimized.

mraaroncruz commented Jul 12, 2014

I had to do this instead of your line 13

create database template1 with owner=postgres encoding='UTF-8'
  lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;

thanks. this was great.

@dvodvo

This comment has been minimized.

dvodvo commented Aug 28, 2014

Too bad I found this after having lost my voice... Kudos, nonetheless. From my perspective hammering down lines makes the syntax and contextual logic sink in better. Grazie.

@christoph88

This comment has been minimized.

christoph88 commented Feb 21, 2015

Thanks!

@knightsamar

This comment has been minimized.

knightsamar commented Apr 14, 2015

+1 to @pferdefleisch. I needed to do that too on Postgresql 9.1

@GerryG

This comment has been minimized.

GerryG commented Jun 21, 2015

It's missnamed, though? You are actually changing template1, which is what is needed. Thanks.

@thomaskonrad

This comment has been minimized.

thomaskonrad commented Oct 9, 2015

Worked like a charm, thanks!

@moeinio

This comment has been minimized.

moeinio commented Oct 14, 2015

how exacly is this can be used?

@sebastianwebber

This comment has been minimized.

sebastianwebber commented Oct 23, 2015

@ffmike, you don't need to connect on template0 to drop/recreate template1.

@varadiz

This comment has been minimized.

varadiz commented Dec 14, 2015

Thank you! Great help, very much appreciate it!

@Troy-Walters

This comment has been minimized.

Troy-Walters commented Jun 9, 2016

Thanks!

@esperandus

This comment has been minimized.

esperandus commented Oct 25, 2016

Yet another thanks :)

@darfat

This comment has been minimized.

darfat commented Dec 10, 2016

Thanks :)

@ghost

This comment has been minimized.

ghost commented Jun 11, 2017

How enable locales are available in PostgreSQL in the pg_collation catalog in window OS. i am using postgresql on windows 2012 server. i will really appropriate your help

@vankooch

This comment has been minimized.

vankooch commented Aug 16, 2017

Hi,

this also works for me

-- Database: postgres
UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8') WHERE datname = 'postgres';
UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8') WHERE datname = 'template0';
UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8') WHERE datname = 'template1';

List of databases \l:

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
@lucianohoffmann

This comment has been minimized.

lucianohoffmann commented Sep 13, 2017

@vankooch solution works for me.

@x16118219

This comment has been minimized.

x16118219 commented Oct 21, 2017

Thank you so so much, I am new to rails and db's and nearly broke google over a full day trying to resolve this

@camilonova

This comment has been minimized.

camilonova commented Nov 20, 2017

@vankooch ❤️

@injune1123

This comment has been minimized.

injune1123 commented Jan 26, 2018

Thank you very much! Your solution works for me.

@ELI7VH

This comment has been minimized.

ELI7VH commented Apr 9, 2018

thank you!

@beatmadsen

This comment has been minimized.

beatmadsen commented Jul 10, 2018

Helped me getting started with the tutorial in the book Programming Phoenix.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment