Create a gist now

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.

Show comment
Hide comment
@rietta

rietta Feb 20, 2013

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

rietta commented Feb 20, 2013

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

@fgrehm

This comment has been minimized.

Show comment
Hide comment

fgrehm commented Apr 22, 2013

@mrahma01

This comment has been minimized.

Show comment
Hide comment
@mrahma01

mrahma01 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;"

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.

Show comment
Hide comment
@AJ-Acevedo

AJ-Acevedo Jul 25, 2013

This was really helpful! Thanks!

AJ-Acevedo commented Jul 25, 2013

This was really helpful! Thanks!

@darkaico

This comment has been minimized.

Show comment
Hide comment
@darkaico

darkaico Jul 30, 2013

Works perfectly! thanks!!

darkaico commented Jul 30, 2013

Works perfectly! thanks!!

@tedstrauss

This comment has been minimized.

Show comment
Hide comment
@tedstrauss

tedstrauss 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 |

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.

Show comment
Hide comment
@rogeralmeida

rogeralmeida Oct 7, 2013

Dude, God bless you! :D

rogeralmeida commented Oct 7, 2013

Dude, God bless you! :D

@omarish

This comment has been minimized.

Show comment
Hide comment
@omarish

omarish commented Jan 16, 2014

👍

@soufianeEL

This comment has been minimized.

Show comment
Hide comment
@soufianeEL

soufianeEL Apr 15, 2014

very helpful .. 👍

soufianeEL commented Apr 15, 2014

very helpful .. 👍

@dusdanig

This comment has been minimized.

Show comment
Hide comment
@dusdanig

dusdanig 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

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.

Show comment
Hide comment
@Depado

Depado Jun 4, 2014

Thanks you're amazing ;)

Depado commented Jun 4, 2014

Thanks you're amazing ;)

@mraaroncruz

This comment has been minimized.

Show comment
Hide comment
@mraaroncruz

mraaroncruz 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.

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.

Show comment
Hide comment
@dvodvo

dvodvo 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.

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.

Show comment
Hide comment
@christoph88

christoph88 commented Feb 21, 2015

Thanks!

@knightsamar

This comment has been minimized.

Show comment
Hide comment
@knightsamar

knightsamar Apr 14, 2015

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

knightsamar commented Apr 14, 2015

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

@GerryG

This comment has been minimized.

Show comment
Hide comment
@GerryG

GerryG Jun 21, 2015

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

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.

Show comment
Hide comment
@thomaskonrad

thomaskonrad Oct 9, 2015

Worked like a charm, thanks!

thomaskonrad commented Oct 9, 2015

Worked like a charm, thanks!

@moeinio

This comment has been minimized.

Show comment
Hide comment
@moeinio

moeinio Oct 14, 2015

how exacly is this can be used?

moeinio commented Oct 14, 2015

how exacly is this can be used?

@sebastianwebber

This comment has been minimized.

Show comment
Hide comment
@sebastianwebber

sebastianwebber Oct 23, 2015

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

sebastianwebber commented Oct 23, 2015

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

@Data-i-Consulting

This comment has been minimized.

Show comment
Hide comment
@Data-i-Consulting

Data-i-Consulting Dec 14, 2015

Thank you! Great help, very much appreciate it!

Data-i-Consulting commented Dec 14, 2015

Thank you! Great help, very much appreciate it!

@Troy-Walters

This comment has been minimized.

Show comment
Hide comment
@Troy-Walters

Troy-Walters commented Jun 9, 2016

Thanks!

@esperandus

This comment has been minimized.

Show comment
Hide comment
@esperandus

esperandus Oct 25, 2016

Yet another thanks :)

esperandus commented Oct 25, 2016

Yet another thanks :)

@darfat

This comment has been minimized.

Show comment
Hide comment
@darfat

darfat Dec 10, 2016

Thanks :)

darfat commented Dec 10, 2016

Thanks :)

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost 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

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.

Show comment
Hide comment
@vankooch

vankooch 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

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.

Show comment
Hide comment
@lucianohoffmann

lucianohoffmann Sep 13, 2017

@vankooch solution works for me.

lucianohoffmann commented Sep 13, 2017

@vankooch solution works for me.

@x16118219

This comment has been minimized.

Show comment
Hide comment
@x16118219

x16118219 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

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.

Show comment
Hide comment

camilonova commented Nov 20, 2017

@vankooch ❤️

@injune1123

This comment has been minimized.

Show comment
Hide comment
@injune1123

injune1123 Jan 26, 2018

Thank you very much! Your solution works for me.

injune1123 commented Jan 26, 2018

Thank you very much! Your solution works for me.

@ELI7VH

This comment has been minimized.

Show comment
Hide comment
@ELI7VH

ELI7VH Apr 9, 2018

thank you!

ELI7VH commented Apr 9, 2018

thank you!

@beatmadsen

This comment has been minimized.

Show comment
Hide comment
@beatmadsen

beatmadsen Jul 10, 2018

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

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