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.

Copy link

rietta commented Feb 20, 2013

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

@fgrehm

This comment has been minimized.

Copy link

fgrehm commented Apr 22, 2013

@mrahma01

This comment has been minimized.

Copy link

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.

Copy link

AJ-Acevedo commented Jul 25, 2013

This was really helpful! Thanks!

@darkaico

This comment has been minimized.

Copy link

darkaico commented Jul 30, 2013

Works perfectly! thanks!!

@tedstrauss

This comment has been minimized.

Copy link

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.

Copy link

rogeralmeida commented Oct 7, 2013

Dude, God bless you! :D

@omarish

This comment has been minimized.

Copy link

omarish commented Jan 16, 2014

👍

@soufianeEL

This comment has been minimized.

Copy link

soufianeEL commented Apr 15, 2014

very helpful .. 👍

@dusdanig

This comment has been minimized.

Copy link

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.

Copy link

Depado commented Jun 4, 2014

Thanks you're amazing ;)

@mraaroncruz

This comment has been minimized.

Copy link

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.

Copy link

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.

Copy link

christoph88 commented Feb 21, 2015

Thanks!

@knightsamar

This comment has been minimized.

Copy link

knightsamar commented Apr 14, 2015

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

@GerryG

This comment has been minimized.

Copy link

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.

Copy link

thomaskonrad commented Oct 9, 2015

Worked like a charm, thanks!

@moeinio

This comment has been minimized.

Copy link

moeinio commented Oct 14, 2015

how exacly is this can be used?

@sebastianwebber

This comment has been minimized.

Copy link

sebastianwebber commented Oct 23, 2015

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

@varadiz

This comment has been minimized.

Copy link

varadiz commented Dec 14, 2015

Thank you! Great help, very much appreciate it!

@Troy-Walters

This comment has been minimized.

Copy link

Troy-Walters commented Jun 9, 2016

Thanks!

@esperandus

This comment has been minimized.

Copy link

esperandus commented Oct 25, 2016

Yet another thanks :)

@darfat

This comment has been minimized.

Copy link

darfat commented Dec 10, 2016

Thanks :)

@ghost

This comment has been minimized.

Copy link

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.

Copy link

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.

Copy link

lucianohoffmann commented Sep 13, 2017

@vankooch solution works for me.

@x16118219

This comment has been minimized.

Copy link

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.

Copy link

camilonova commented Nov 20, 2017

@vankooch ❤️

@injune1123

This comment has been minimized.

Copy link

injune1123 commented Jan 26, 2018

Thank you very much! Your solution works for me.

@ELI7VH

This comment has been minimized.

Copy link

ELI7VH commented Apr 9, 2018

thank you!

@beatmadsen

This comment has been minimized.

Copy link

beatmadsen commented Jul 10, 2018

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

@gamenetsina

This comment has been minimized.

Copy link

gamenetsina commented Dec 21, 2018

tank you @ffmike AND @vankooch

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