Change encoding in PostgreSQL 9.1

Question

I have the following databases

sudo -u postgres psql -c "\list"

                             List of databases
   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 | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

How can I change encoding from LATIN1 to UTF8 in the database template1 or template0?

are you aware that those are very special databases, no? postgresql.org/docs/9.2/static/manage-ag-templatedbs.html — leonbloy
– leonbloy, Commented Nov 27, 2012 at 15:44

Community · Accepted Answer · 2017-05-23 12:07:25Z

4

Since you don't appear to have any actual data here, just shutdown and delete the cluster (server and set of databases) and re-create it. Which operating system are you using? The standard PostgreSQL command to create a new cluster is initdb, but on Debian/Ubuntu et al you'd typically use pg_createcluster

See also How do you change the character encoding of a postgres database?

Although you can try to tweak the encodings, it's not recommended. Even though I suggested it in that linked question, if you had data with latin1 characters here, you'd need to recode them to utf-8.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Nov 27, 2012 at 14:13

araqnid

135k25 gold badges164 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Weyner Arnone Aguilera · Accepted Answer · 2017-09-28 13:48:06Z

0

Just use:

update pg_database set encoding = pg_char_to_encoding('LATIN1') where datname = 'seguros'

answered Sep 28, 2017 at 13:48

Weyner Arnone Aguilera

112 bronze badges

Collectives™ on Stack Overflow

Change encoding in PostgreSQL 9.1

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related