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

@rietta 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 fgrehm commented Apr 22, 2013

@mrahma01

This comment has been minimized.

Copy link

@mrahma01 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 AJ-Acevedo commented Jul 25, 2013

This was really helpful! Thanks!

@darkaico

This comment has been minimized.

Copy link

@darkaico darkaico commented Jul 30, 2013

Works perfectly! thanks!!

@tedstrauss

This comment has been minimized.

Copy link

@tedstrauss 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 rogeralmeida commented Oct 7, 2013

Dude, God bless you! :D

@omarish

This comment has been minimized.

Copy link

@omarish omarish commented Jan 16, 2014

👍

@soufianeEL

This comment has been minimized.

Copy link

@soufianeEL soufianeEL commented Apr 15, 2014

very helpful .. 👍

@dusdanig

This comment has been minimized.

Copy link

@dusdanig 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 Depado commented Jun 4, 2014

Thanks you're amazing ;)

@mraaroncruz

This comment has been minimized.

Copy link

@mraaroncruz 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 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 christoph88 commented Feb 21, 2015

Thanks!

@knightsamar

This comment has been minimized.

Copy link

@knightsamar 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 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 thomaskonrad commented Oct 9, 2015

Worked like a charm, thanks!

@moeinio

This comment has been minimized.

Copy link

@moeinio moeinio commented Oct 14, 2015

how exacly is this can be used?

@sebastianwebber

This comment has been minimized.

Copy link

@sebastianwebber 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 varadiz commented Dec 14, 2015

Thank you! Great help, very much appreciate it!

@Troy-Walters

This comment has been minimized.

Copy link

@Troy-Walters Troy-Walters commented Jun 9, 2016

Thanks!

@esperandus

This comment has been minimized.

Copy link

@esperandus esperandus commented Oct 25, 2016

Yet another thanks :)

@darfat

This comment has been minimized.

Copy link

@darfat darfat commented Dec 10, 2016

Thanks :)

@ghost

This comment has been minimized.

Copy link

@ghost 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 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 lucianohoffmann commented Sep 13, 2017

@vankooch solution works for me.

@x16118219

This comment has been minimized.

Copy link

@x16118219 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 camilonova commented Nov 20, 2017

@vankooch ❤️

@injune1123

This comment has been minimized.

Copy link

@injune1123 injune1123 commented Jan 26, 2018

Thank you very much! Your solution works for me.

@ELI7VH

This comment has been minimized.

Copy link

@ELI7VH ELI7VH commented Apr 9, 2018

thank you!

@beatmadsen

This comment has been minimized.

Copy link

@beatmadsen 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 gamenetsina commented Dec 21, 2018

tank you @ffmike AND @vankooch

@micthiesen

This comment has been minimized.

Copy link

@micthiesen micthiesen 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

@railsfactory-pavan railsfactory-pavan commented Aug 8, 2019

Thanks

@colinwilson

This comment has been minimized.

Copy link

@colinwilson colinwilson commented Oct 23, 2019

@vankooch Thank you!

@javimolla

This comment has been minimized.

Copy link

@javimolla javimolla commented Apr 18, 2020

@ffmike, Thank you very much!

I was having trouble with a new Phoenix project in Windows 10 because of the default PG encoding (it was 1252 and it needed to be UTF8), but the error was not very helpful (executing mix exto.create returned argument error (stdlib 3.8) :io.put_chars(:standard_error, :unicode [[[], <<42, 42, 32, 40...)

@DYernar

This comment has been minimized.

Copy link

@DYernar DYernar commented Apr 24, 2020

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

thank you it worked

@georgedorn

This comment has been minimized.

Copy link

@georgedorn georgedorn commented May 29, 2020

Note this is also a decent way to fix the recent collation breakage from distros updating underlying locale behavior in glibc breaking expected sorting. In my case I ended up doing this during creation of template1, in order to get postgresql's sorting to match python's.

create database template1 with template = template0 LC_COLLATE='C';
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.