How would i go about changing a databases encoding? Is this at all possible?
There does not seem to be much i can with ALTER DATABASE except change its name!
You could try to: -pg_dump the database to file, -drop the database, -recreate the database with unicode encoding (createdb mynewdb -e unicode), -check the dumped file for any 'strange' characters and change as needed, -restore to the new database
PS I will have to do this soon to several databases. If you do convert yours, please post a follow-up message on this thread outlining your experience.
Here are the steps from a previous thread for a conversion to a different language (I couldn't find the link so I'm pasting it into this message):
--------------------------------------------------------------------------------------------------------------------------------
Re: [GENERAL] Converting SQL-ASCII encoding database to UNICODE
Jean-Michel POURE wrote:
Le Dimanche 9 Novembre 2003 19:39, Rajesh Kumar Mallah a écrit :\--------------------------------------------------------------------------------------------------------------------------------
If so what is the process
The advantage of using a Unicode database is that UTF-8 supports/includes all known encodings at once. Therefore, in the process of development, it can help you save time.
When using a Unicode database, if the client does not support Unicode, it is always possible to recode data streams on the fly with "set client_encoding = foo_encoding". Therefore, there is no real drawback in using Unicode. It may only be a little slower, but there is no real proof.
The process of conversion is as follows:
- dump the whole database using pg_dump: pg_dump database_name > pg_data_dump.sql
- Do no drop the old database. Make a backup of the dump: cp pg_data_dump.sql pg_data_dump.sql.bak
- recode the dump using the GNU recode package: recode ..u8 pg_data_dump.sql
recode will try to guess the encoding the original ASCII database.
- Open the file in an UTF-8 editor like Kate and verify that all characters are preserved and display well (in Hindi?). If it does not work, it may be a problem of original encoding. Try to guess it and retry using:
cp -f source_encoding..u8.bak source_encoding..u8 recode source_encoding..u8 pg_data_dump.sql
- create an empty Unicode database: psql template1 create database new_database with encoding=Unicode; reload the dump: psql new_database < pg_data_dump.sql
GNU/recode is available in most GNU/Linux distributions.
By the way, as you live in India, do not hesitate to visit http://pgadmin.postgresql.org/pgadmin3/translation.php if you can help us.
Most translations in languages for India are "stalled", do not hesitate to take over the translation in Hindi for example.
Cheers, Jean-Michel
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
hth Ron
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
