Re: Change database encoding

> > 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