Thread: Change database encoding

Hi All,

When I originally built a database I did not understand the ramifications of
choosing the encoding.  Not knowing any better I chose SQL-ASCII.  Since
upgrading pgadmin3 to the latest release I am cautioned that I should switch to
UNICODE.

How do I go about changing the database encoding?

Is UNICODE the 'best' choice for a general purpose database?

TIA

Kind Regards,
Keith

I guess you can not alter database encodng, so the way to change database encoding can be following these steps:

1. Take the database dump for which you wish to change encodig using pg_dump.

2. Now drop the database

3. Recreate the database with the encoding you wish to

For exampe --> createdb testdb -e unicode

4. Check if the dump file created in the first step has any special characters and do the required changes.

5. Now you can restore the dump file into the newly created database.

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.

/Shoaib Mir

> > On 3/28/06, Keith Worthington <keithw@narrowpathinc.com> wrote:
> >
> > Hi All,
> >
> > When I originally built a database I did not understand the
> > ramifications of choosing the encoding.  Not knowing any better
> > I chose SQL-ASCII.  Since upgrading pgadmin3 to the latest
> > release I am cautioned that I should switch to UNICODE.
> >
> > How do I go about changing the database encoding?
> >
> > Is UNICODE the 'best' choice for a general purpose database?
> >
> > TIA
> >
> > Kind Regards,
> > Keith
>
> On Tue, 28 Mar 2006 21:34:41 +0500, Shoaib Mir wrote
> I guess you can not alter database encodng, so the way to change
> database encoding can be following these steps:
>
> 1. Take the database dump for which you wish to change encodig
>    using pg_dump.
>
> 2. Now drop the database
>
> 3. Recreate the database with the encoding you wish to
>    For exampe --> createdb testdb -e unicode
>
> 4. Check if the dump file created in the first step has any special
>    characters and do the required changes.
>
> 5. Now you can restore the dump file into the newly created database.
>
> 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.
>
> /Shoaib Mir

Shoaib,

Thank you very much for the guidance.  For posterity's sake using bash
I ended up with the following command.

vacuumdb --full --analyze --username postgres --dbname MYDB && pg_dump MYDB -Ft
-v -U postgres -f tmp/MYDB.tar && dropdb MYDB --username postgres && createdb
--encoding UNICODE MYDB --username postgres && pg_restore tmp/MYDB.tar | psql
--dbname MYDB --username postgres && vacuumdb --full --analyze --username
postgres --dbname MYDB

Kind Regards,
Keith