Skip to content

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

commented Feb 20, 2013

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

@fgrehm

This comment has been minimized.

Copy link

commented Apr 22, 2013

@mrahma01

This comment has been minimized.

Copy link

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

commented Jul 25, 2013

This was really helpful! Thanks!

@darkaico

This comment has been minimized.

Copy link

commented Jul 30, 2013

Works perfectly! thanks!!

@tedstrauss

This comment has been minimized.

Copy link

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

commented Oct 7, 2013

Dude, God bless you! :D

@omarish

This comment has been minimized.

Copy link

commented Jan 16, 2014

👍

@soufianeEL

This comment has been minimized.

Copy link

commented Apr 15, 2014

very helpful .. 👍

@dusdanig

This comment has been minimized.

Copy link

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

commented Jun 4, 2014

Thanks you're amazing ;)

@mraaroncruz

This comment has been minimized.

Copy link

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

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

commented Feb 21, 2015

Thanks!

@knightsamar

This comment has been minimized.

Copy link

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

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

commented Oct 9, 2015

Worked like a charm, thanks!

@moeinio

This comment has been minimized.

Copy link

commented Oct 14, 2015

how exacly is this can be used?

@sebastianwebber

This comment has been minimized.

Copy link

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

commented Dec 14, 2015

Thank you! Great help, very much appreciate it!

@Troy-Walters

This comment has been minimized.

Copy link

commented Jun 9, 2016

Thanks!

@esperandus

This comment has been minimized.

Copy link

commented Oct 25, 2016

Yet another thanks :)

@darfat

This comment has been minimized.

Copy link

commented Dec 10, 2016

Thanks :)

@ghost

This comment has been minimized.

Copy link

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

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

commented Sep 13, 2017

@vankooch solution works for me.

@x16118219

This comment has been minimized.

Copy link

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

commented Nov 20, 2017

@vankooch ❤️

@injune1123

This comment has been minimized.

Copy link

commented Jan 26, 2018

Thank you very much! Your solution works for me.

@ELI7VH

This comment has been minimized.

Copy link

commented Apr 9, 2018

thank you!

@beatmadsen

This comment has been minimized.

Copy link

commented Jul 10, 2018

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

@gamenetsina

This comment has been minimized.

Copy link

commented Dec 21, 2018

tank you @ffmike AND @vankooch

@micthiesen

This comment has been minimized.

Copy link

commented Mar 2, 2019

This SQL also works if you want to continue using template0:

/* Allow modification of template0 */
UPDATE pg_database SET DATALLOWCONN = TRUE WHERE DATNAME = 'template0';

/* Create template1 as a temporary template */
\c template0
UPDATE pg_database SET DATISTEMPLATE = FALSE WHERE DATNAME = 'template1';
DROP DATABASE IF EXISTS template1;
CREATE DATABASE template1 WITH
  TEMPLATE = template0
  ENCODING = 'UTF8'
  LC_CTYPE 'en_US.UTF8'
  LC_COLLATE 'en_US.UTF8';

/* Drop then re-create template0 with the correct encoding */
\c template1
UPDATE pg_database SET DATISTEMPLATE = FALSE WHERE DATNAME = 'template0';
DROP DATABASE template0;
CREATE DATABASE template0 WITH
  TEMPLATE = template1
  ENCODING = 'UTF8'
  LC_CTYPE 'en_US.UTF8'
  LC_COLLATE 'en_US.UTF8';
UPDATE pg_database SET DATISTEMPLATE = TRUE WHERE DATNAME = 'template0';

/* Clean up the temporary template */
\c template0
UPDATE pg_database SET DATALLOWCONN = TRUE WHERE DATNAME = 'template1';
DROP DATABASE template1;

Or if you want both template0 and template1:

/* Allow modification of template0 */
UPDATE pg_database SET DATALLOWCONN = TRUE WHERE DATNAME = 'template0';

/* Create template1 with the correct encoding */
\c template0
UPDATE pg_database SET DATISTEMPLATE = FALSE WHERE DATNAME = 'template1';
DROP DATABASE IF EXISTS template1;
CREATE DATABASE template1 WITH
  TEMPLATE = template0
  ENCODING = 'UTF8'
  LC_CTYPE 'en_US.UTF8'
  LC_COLLATE 'en_US.UTF8';
UPDATE pg_database SET DATISTEMPLATE = TRUE WHERE DATNAME = 'template1';

/* Drop then re-create template0 with the correct encoding */
\c template1
UPDATE pg_database SET DATISTEMPLATE = FALSE WHERE DATNAME = 'template0';
DROP DATABASE template0;
CREATE DATABASE template0 WITH
  TEMPLATE = template1
  ENCODING = 'UTF8'
  LC_CTYPE 'en_US.UTF8'
  LC_COLLATE 'en_US.UTF8';
UPDATE pg_database SET DATISTEMPLATE = TRUE WHERE DATNAME = 'template0';
@railsfactory-pavan

This comment has been minimized.

Copy link

commented Aug 8, 2019

Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.