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

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.

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

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

To expand on the answers given, you can use these commands to accomplish your task.

// Backup the database to outfilepg_dump dbname > outfile// Terminate all connections to the databasepsql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='dbname';"// Delete the databasepsql -c "DROP DATABASE dbname;"//Re-create the database using the encoding keywordpsql -c "CREATE DATABASE dbname ENCODING='UTF8';"//Import the saved datapsql -f outfile

Dumping database: http://www.postgresql.org/docs/9.4/static/backup-dump.html

Creating database: http://www.postgresql.org/docs/9.4/static/sql-createdatabase.html

This is a list of all encodings available for version 9.4:http://www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED