Skip site navigation (1) Skip section navigation (2)

Re: Change database encoding

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "Shoaib Mir" <shoaibmir(at)gmail(dot)com>
Cc: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Change database encoding
Date: 2006-03-29 20:43:40
Message-ID: 20060329203545.M43728@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
> > On 3/28/06, Keith Worthington <keithw(at)narrowpathinc(dot)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

In response to

pgsql-novice by date

Next:From: george youngDate: 2006-03-30 15:09:12
Subject: Re: Does a connection support multiple transactions.
Previous:From: John DeSoiDate: 2006-03-29 18:32:50
Subject: Re: PHP libpq Version != Actual PGSQL Version

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group