How can I change database encoding for a PostgreSQL database using sql or phpPgAdmin?

Question

How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?

Answer 1

In short, you cannot do this with only phpPgAdmin or SQL without risking existing data corruption. You have to export all data, create database with correct encoding and restore exported data.

This is how you should proceed:

1. create database dump:

   pg_dump your_database > your_database.sql

   this will save your database in sql format, in encoding you currently have.

2. delete database (or rename it):

   DROP DATABASE your_database

   if you have enough storage to do it I recommend leaving old database until you make sure everything is OK with new one, rename it:

   ALTER DATABASE your_database RENAME TO your_database_backup;

3. create database with new encoding:

   CREATE DATABASE your_database WITH ENCODING 'UNICODE' TEMPLATE=template0;

4. import data from dump created before:

   PGCLIENTENCODING=YOUR_OLD_ENCODING psql -f your_database.sql your_database

   you need to set psql client encoding to one you had in old database.

Changing encoding on-the-fly isn't possible as it would require rewriting most of internal database data which is almost equal to recreating db way I described.

It is possible to just alter internal postgres informations about database and any new data after this alteration will be saved correctly, however your existing data might get corrupted.

Answer 2

You can change encoding on the fly without dump/restore:

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'database_name'