Create a gist now

Instantly share code, notes, and snippets.

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 commented Feb 20, 2013

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

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

This was really helpful! Thanks!

Works perfectly! thanks!!

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 |

Dude, God bless you! :D

omarish commented Jan 16, 2014

👍

very helpful .. 👍

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

Depado commented Jun 4, 2014

Thanks you're amazing ;)

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

Thanks!

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

GerryG commented Jun 21, 2015

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

Worked like a charm, thanks!

moeinio commented Oct 14, 2015

how exacly is this can be used?

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

Thank you! Great help, very much appreciate it!

Thanks!

Yet another thanks :)

darfat commented Dec 10, 2016

Thanks :)

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 commented Aug 16, 2017 edited

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 solution works for me.

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