Changing the 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 to change encoding from LATIN1 to UTF8 in the database template1 or template0 ?

Answer 1

Since you do not have real data, just close and delete the cluster (server and database set) and recreate it. What operating system are you using? The standard PostgreSQL command to create a new cluster is initdb, but in Debian / Ubuntu et al you usually use pg_createcluster

See also How to change character encoding in postgres database?

Although you can try to configure encodings, this is not recommended. Despite the fact that I suggested this in this related question, if you had data with Latin characters here, you will need to copy them to utf-8.

Answer 2

Just use:

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