PostgreSQL Newbies - Change database encoding

Author

Change database encoding

Keith Worthington

2006-03-28, 9:30 am

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Shoaib Mir

2006-03-28, 11:31 am

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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Keith Worthington

2006-03-29, 8:27 pm

> > On 3/28/06, Keith Worthington < keithw@narrowpathinc
.com> wrote:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links